Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I converted an Excel power Pivot model to Power BI. When I exported to analyze excel because I want to create the report in excel for a user that only likes excel I realized I need to create some other measures that I didn't set up initially. However, when I go to the workspace I have created to get to the model I see the data set but when I click I don't see all the dimension tables I have created in my model. In addition, I am not sure why the model has not been refreshed as of now. Can you please give me some guidance? I am a beginner level and I am stuck in this step.
Solved! Go to Solution.
You are correct - while Power BI Desktop can work with implicit measures, "Analyze in Excel" requires explicitly defined measures.
The usual approach is to create these measures in Power BI Desktop, then hide the original columns (as they are not useful any more), then to republish the Power BI Desktop file to the workspace (and optionally to update the app). Then in your Excel you right click on the Pivot table and click "Refresh" , or click "Refresh all" in the ribbon, and your newly defined measures should now appear and be usable in the Values area.
Do what the message says - click in that area to work with the pivot table. This will bring up all the tables from your dataset. Add the fields as needed. Then you an also refresh whenever necessary
Is it easier to create the new measures in my power query model or in the power Bi model?
You cannot create measures in Power Query. This needs to be done in DAX.
You are correct - while Power BI Desktop can work with implicit measures, "Analyze in Excel" requires explicitly defined measures.
The usual approach is to create these measures in Power BI Desktop, then hide the original columns (as they are not useful any more), then to republish the Power BI Desktop file to the workspace (and optionally to update the app). Then in your Excel you right click on the Pivot table and click "Refresh" , or click "Refresh all" in the ribbon, and your newly defined measures should now appear and be usable in the Values area.
I have converted an Excel power pivot model to Power BI. When I go analyze excel to create a pivot table I realize the data has not been refreshed only shows until the period May 21. I need the data to be refreshed as of October 2021. I also need to create new measures. What is the best way to do that? I have done many courses but I need some guidance I feel a bit lost right now. Thanks in advance for your help
Select "Refresh All" in your Excel file, that will pull the latest data from the Power BI dataset.
i, Thanks for your quick response. However, let me explain what I did I have created a model in power query that I have been using to develop several reports. Then I wanted to use the model in Power BI and I have coverted the model from power pivot to power bi. However, suggested by my teammate that knows a lot but he doesn't teach me much that I should use analyze excel to do the report. At that point, I realize the data of the model had not been updated. However, in power query the way I do it is I go to live data refresh select new months saved and close and then go to the Power query model to update the raw data. I am not sure how can I verify if the model is updated and how to update it. Please send me any tutorials or recommendations to show all these steps. somebody has shown me this very quickly and I feel lost at the moment.
Do what the message says - click in that area to work with the pivot table. This will bring up all the tables from your dataset. Add the fields as needed. Then you an also refresh whenever necessary
Hi, Thank you for your time and help. I was accessing the model through service and through a power query on the desktop app. Now I can see all the tables and fields and I will start to create additional measures and make other transformations for the final report I need to create in excel.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
37 | |
30 | |
18 | |
14 | |
8 |
User | Count |
---|---|
50 | |
39 | |
32 | |
15 | |
13 |