I have only recently learned how to do some things in Power Query. The data clean up functionality is just fantastic. I am trying to transition some older regular excel workbooks over to using power query and am not sure how this will work.
What I currently do:
I have a single table that is linked to 4-5 subsequent tabs with pivot tables on them. I do all of my data scrubbing in my source system so that my export file is formatted perfectly for my pivot tables. I instruct users to simply copy and paste the exported excel data (post scrubbing) and paste it into my "Source Table" in the workbook I give them.
This process works and isn't hard for my users b/c its a simple, copy, paste, refresh.
What I want to do:
I'd like to pull out a standard exported excel file and then clean it up in PowerQuery. From Power Query I can get a clean table of data and then link my pivot tables to that table.
My question is if this will cause any problems for my users if they haven't installed power query? Do they have to have the add in? or is that PQ functionality built into the workbook that i create and give to them?
Also, can anyone explain the advantages of "import and load to..." a connection versus just loading back to a table? Is it just for large data sets? I am mostly working w/ less than 20K rows, so loading the table back into workbook is generally ok.
I appreciate any thoughts.
Bookmarks