I have a report in Power BI that uses data from an Excel file. Is it better to have a very slim Excel table with values only and add all (possible) calculations via custom columns in Power Query or should I make all calculations in the Excel file before importing the data?
3
-
Try it both ways and see which works better for you.– Ron RosenfeldCommented Jan 10 at 18:12
-
Whatever is decided, try to make it consistent. In other words, if possible avoid doing some transformations (calculations) in excel while others in power query, especially if there is no reasoning why that split was chosen.– gns100Commented Jan 10 at 18:47
-
I agree re: consistency. I would add this: Ask yourself if the calculations for your Power BI report are needed by any other use of the Excel file. If so, put them in the Excel file. If not, put them in Power Query. If you can have the Excel file as a csv or txt, then do that instead.– FlexYourDataCommented Jan 11 at 17:36
Add a comment
|
1 Answer
I would go with the first option - use Power Query. My main reason would be to keep all the logic for the Power BI solution in one place - Power BI, rather than having some in Excel and the rest in Power BI.
Within Power BI, you have all the Power Query features which are roughly equivalent to Excel functions, but quite a bit easier to build and test. You can also add DAX columns and measures, using the semantic model.
Looking down the track as new data comes in, all the Power BI logic will automatically re-run the same way every time. Calculations in Excel will rely on a skilled user to apply them to new/changed data.
Performance and scalability isn't usually a major consideration for solutions on this architecture, but if it is, the semantic model data compression in Power BI is far more efficient than Excel.
