Let's say you have Excel open, you're in a blank workbook, and you want to grab some data from Power BI. You've heard about this really nice Power BI report that everyone's been talking about, and you want to grab some data and bring it into Excel. Now, you know the report is called "HQ Final Report 2023." You go back to your blank Excel workbook, you go to Data, Get Data, From Power Platform, From Power BI..
Now, on the side pane, you immediately get to see anything that's promoted or endorsed. This data set that I want isn't endorsed yet, so I'm just going to search for it by typing in "2023," and I see it here. Now, here's the cool thing. We are able now to insert a table from this data set, not just a pivot table, but also a table. So, let's test this option first. I'm going to click on Insert Table, and I get this really neat interface pop-up. Under Data,.
I can see all the different tables and KPIs, any measures that exist in this data set. I'm able to import a specific table. So, let's say I want to take a look at our Store's Master Data. I just place a checkmark for Stores, and I get to see everything here. I can directly insert this in Excel, or I can create a table that I want by making my selections from here. So, let's say I just want to get Store Name, and I want to take a look at Sales by Store Name. So,.
I'm going to go to KPI. Let's scroll down. There is Sales Innovative. Yes, let's go for that. There is Sales Online, and there is Total Sales. I'm going to add these in. I don't really want them in this order, so I'm going to go to Build and switch the order. I want Total Sales first, then Sales Online, and Innovative. It's just a matter of dragging and dropping. Now, let's say I want to add some filters. I can either add a filter to an existing category.
Table in Excel. This is a connected table that I can refresh anytime to grab the latest data. So notice the number formatting isn't pulling through, I'm going to go ahead and adjust that. But one thing I need to do before that is to remove the table formatting, so that I can create my own nature design, and that it's not so busy like it was before. Let's select these columns, go and adjust the number formatting to a currency. Let's also remove the decimal places. Now,.
You can go ahead and create any reports that you need based on this data. You can use this information in any way you see fit. You can add formulas, you can add conditional formatting, and best of all, this is connected to Power BI. You can check your connection properties by going to Data, Queries & Connections, and you are going to see it pop up here under Connections. Now, when I right-mouse click and go to Properties, I can update my refresh options. So,.
If I want to refresh it every 15 minutes, I can update that. I can refresh the data when opening the file. I can go to Definition, just take a look at my query definition, and I can check where this is currently being used in. So, this is the current location in this sheet of my workbook. Now, let's take a look at your second option, which is inserting a pivot table. So, if your starting point is Excel, you have two different options to insert a pivot table from.
Power BI. You can either go to Data, Get Data, From Power Platform, From Power BI, or a faster way might be just to go to Insert, PivotTable, From Power BI, and you're going to come to the same place. You get to see your data sets here, select the one you need or just search for what you need and insert your pivot table. Now you're going to get a blank pivot table here with all your fields inside the Pivot Table Fields, and it's just a matter of selecting what you want..
Let's say we want to have our Product Hierarchy in the rows, and I want to be able to take a look at some KPIs, for example, my Gross Profit and the number of orders. Notice the advantage of using a pivot table is that our number formatting comes over this time. We can drill down into our hierarchy or let's say I actually don't want Product Hierarchy. I'm going to kick it out. Instead, I want to take a look at the breakdown by year, quarter, and so on. So,.
- How To Create A Drag & Drop Family Tree Maker In Excel FREE DOWNLOAD
- How To Add Responsive Course Cards To Your Blogger Website BlogSpot
- Python Part 14d - Working with strings Office
I'm going to bring over the Date Hierarchy instead, and then I can drill down as I need to. - ID Card Make
Now, remember, all of this is connected to Power BI, so anytime you want to get the latest data, you can right-mouse click and select Refresh or update your refresh settings accordingly. The next method we're going to take a look at is called Analyze in Excel. So, this time our starting point is Power BI. Let's say we're logged into the Power BI app,.We have this report opened, and we want to take a look at some other information that might not be visualized for us in this report. By the way, if you would like to learn how to create this report from scratch, I cover it in our course called "Fast Track to Power BI," and I'm going to have the link to it in the description of this video. So, what you're going to do if you want to create your own analysis in Excel is you're.
Going to go to Export here and select Analyze in Excel. This is going to open Excel for the web, and it's going to create a file that's going to be saved in your OneDrive. Now, you get this pop-up to be careful because this workbook contains queries. Well, that's fine because you're connecting to the Power BI data set. Now, we get our pivot table placeholder, and we can build and create our pivot.
Table here. Let's say I want to use the Date Hierarchy. I'm going to add this to the rows, and I can add any measures that I want to my values field. But even if you don't have any measures created, you are able to use implicit measures. So, for example, I have a Quantity column, and I'm just going to drag and drop it to the Values field, and my pivot table is automatically going to summarize this for me. Now, of course,.
Best practice is to use measures instead. So, if you happen to have a Quantity measure, you want to use that one instead because you're going to have the formatting already in place for you. Now, if you feel more comfortable working in Excel for desktop, you can open it in the desktop app, and then you can continue working on it in the desktop version of Excel. The next option you have to export your data to Excel is called Export Data, but you get to.
Export it with a live connection. So, let's say I'm interested in the underlying data that makes up this specific chart. I'm going to go here to More Options and select Export Data. Here, we can export our summarized data that's used to create this visual, and under File Format, I have the ability to connect to it via a live connection. So, the other options are just XLSX and CSV, but I want a live connection, so I'm going to select.
That and click on Export. This time, the file lands in my Downloads folder, so I'm just going to go ahead and open the file. At first glance, I see nothing. I have to enable editing. Then, I need to enable the content to be able to see my values. They're all nicely organized in a table. In a second tab, I have Export Details. I can see that the data is connected to Power BI. Click Refresh to update, and you're going to be able to see any applied filters. So,.
Currently, we just have one filter in this year is greater than 2019. Now, we can go ahead and update this table as we need. So, let's say I'm going to add the currency format here. I also want to add some conditional formatting to this, but first, I'm going to go ahead and remove the table style. Now, let's go back and add conditional formatting. I'll just add something simple. Let's go with a color scale here..
Okay, so we can see our data goes until the end of August. Now, what happens when our data set is updated, and let's say we've received data for the next month? Well, all I have to do is to refresh this. Let's give it a go. Let's go ahead and refresh, and the data for the next month pulls over. Our conditional formatting also pulls through. Now, you might be asking, what about the other reports that we originally created? I originally.
Started from Excel and I created reports that were connected to Power BI. One was this pivot table,
And the other one was this table. Well, they will refresh as well. So, I'm going to click on Refresh All, and we are going to get the latest data pulled through. If I go to my pivot table, I have September pop up here. All connections are live. Before we wrap up, let's talk about some prerequisites to get this connection to.Excel to work properly. There are two settings that need to be enabled by your admin in tenant settings. One is for Excel Live Connection, and the other one is for Analyze in Excel. Then you also have to make sure that you have edit rights to the Power BI data set or at least the contributor role in the Power BI workspace that contains the data set. You also have to make sure that you have a suitable Power BI license for that workspace..
