Forum Discussion
Adding New Queries Takes Forever
Hi ddbaker - no, you can do the transformations in Power Query.
- Do the combine operation as normal.
- You will have a large listing of data. That needs to be cleaned up.
- Go to the "Sample Query" that Power Query created as part of the combine operation. From there, do the individual unpivots, renaming, etc. So you are transforming one file there.
- Power Query will then use that logic and apply to every file and drop it in your final combined query.
Hi edhans, thank you so much for the response. I apologize for the delay in getting back to you. I didn't know about the Combine feature in Power Query, so thank you for telling me about it. I've been trying it out this morning and doing some research, and I'm running into a couple of issues. First, the files I download are CSV exports of our Profit and Loss and Balance Sheets from QuickBooks. Each export goes through the exact same steps to transform it into "flat" data (i.e. Unpivoting, renaming columns, etc.) that can be used for analysis in PBI. Hence my first approach of transforming each individual export (by copying and pasting the code and changing the source) and then appending into a single query. If I were to use the "Combine" feature, would I have to transform each individual file (in Excel Power Query or something similar) and then upload each transformed file into a folder that can be used to Combine all the data? I'm willing to take the time to do this, but I want to make sure there isn't a better way before I spend the time doing it. I appreciate your help!
Hi ddbaker - no, you can do the transformations in Power Query.
- Do the combine operation as normal.
- You will have a large listing of data. That needs to be cleaned up.
- Go to the "Sample Query" that Power Query created as part of the combine operation. From there, do the individual unpivots, renaming, etc. So you are transforming one file there.
- Power Query will then use that logic and apply to every file and drop it in your final combined query.