Log In
Register
Home
Active Member
April 8, 2022
Offline
Hello,
I have an external csv file that I'm using power query to clean up. The result is a Connection containing the required data. From this data I need to create several different Pivot Tables. Is there something like a Best Practice to solve this situation? I have seen an absolutely amazing tutorial
, but I don't know which of the solutions listed to use in my situation. Is it to use an intermediate step and load the data into the Data Model and create a Power Pivot Table from it? Or create multiple copies of Power Query (Connection) for each different Pivot Table etc.
Thank you for your advice
Libor
See Answer
July 16, 2010
Offline
Hi Libor,
The answer partly depends on which version of Excel you have and partly on whether you need Power Pivot and DAX function capabilities.
Either way, you wouldn't want to create multiple copies of the query. Just create one query and either load it to Power Pivot or to the Excel worksheet, or if you have the option, Load to a PivotTable. Then you can copy the first PivotTable and modify it for your other PivotTables.
Mynda
Active Member
April 8, 2022
Offline
Hi Mynda,
Thank you for your reply. I use MS Office 2019 and MS Office 2021.
Case 1
If I understand correctly, when I don't need DAX, I load the Power Query result directly into the Pivot Table. And if I need to create multiple different Pivot Tables from one Power Query, I simply copy the first Pivot Table as many times as I need and edit it to the form I need.
Case 2
If I need a DAX, then of course I have to load the result of the Power Query into the Data Model and use that as the source for the first Power Pivot. And the next procedure is the same as in the first case. I will copy the Power Pivot and modify this copy to the desired form.
If I watched your video well, in both these cases there should be no problem to autorefresh all the copies of Pivot Table / Power Pivot with a single click on the "Refresh All" button. Without having to click this button multiple times. Is that right?
Are there any advantages or disadvantages to using case 2 even if I don't need DAX right now? Because it is possible that I may need it in the future. My Pivot Tables are very complex and I haven't found a way to copy the Pivot Table settings from one copy to another copy.
Thank you very much
Libor
Answers Post
July 16, 2010
Offline
"In both these cases there should be no problem to autorefresh all the copies of Pivot Table / Power Pivot with a single click on the "Refresh All" button. Without having to click this button multiple times. Is that right?" Correct.
There's no major disadvantages to using Power Pivot even if you don't need DAX right now, so if you think there's a chance you could need it in the future, then I'd use Power Pivot.
Mynda
Active Member
April 8, 2022
Offline
Hi Mynda,
thanks again for the reply. I've been dealing with the autorefresh issue for some time, but only now I'm clear on it.
I've been looking at some other videos on your youtube channel and always find something useful there. I will definitely look at the other ones as well. I just have to find the time because there are so many of these videos :o)
Have a great day
Libor
The following users say thank you to Libor Theimer for this useful post:
Mynda Treacy
1 Guest(s)
