Forum Discussion
Change Data Source but Keep Models and Visualizations
I like this approach for automation, thanks for suggesting the approach. For manual updates I just use the Change Source dialog in Data Source Settings:
If you want to extract the data behind a pivot-table in Excel, you would have to adress that source directly instead of the pivot. The Pivot table in Excel doesn't carry any metadata about it that could be retrieved by Power BI.
I see your point, but when i double click on the excel pivot, it generates the data behind that pivot, apparently i was wondering why Power BI is not capable of the same technique
in my case i have files stored in xlsm pivoted format, every time when i need to load that file into Power BI, i would need to unpivot the data first(by double clicking) and then load it to Power BI. there is an additional step which is coming in and making things looks weird.
Do you have any solution to avoid that double clicking and generate the data within Power BI?
I don't know why Microsoft has implemented it that way. There are a couple of other data as well (like formatting for example) that don't come with the standard Power Query-import.
Only thing you can do is to hack the Excel-file and re-assemble the xml to the table's content: https://github.com/ImkeF/Power-Query-Excel-Formats
But that might get very cumbersome (no support on that from me here currently).
I've written about this method here: https://www.thebiccountant.com/2017/08/18/how-to-import-from-excel-with-cell-coordinates-in-power-query-and-power-bi/