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:
Hi CJ,
best practice for this scenario is to use a parameter for your connection-string to the database (or at least for the database name) that you use/reference in all your queries.
That way you only have to change the parameter once and all your data that uses/references this parameter will be switched to the new datasource at once.
how would this work with Excel Pivot data. i am not able to find a solution where Power BI connects to an excel (Pivotted excel) and can extract the data behind that pivot.
any suggestion on this would be highly appreciable
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).