Split master file by column values/ rename PQ output file?

[njs27]

Hi all,

I'm trying to use PQ to import a file (connection only) and then split the data into separate workbooks based on the value in the second column (there are a bunch of divisions, and each division's data would be on a separate workbook as the end state). See the example workbook attached.

I have created 2 functions in PQ: fxCn&CleanData (to connect to the table and clean the data), and fxFilterRows (to filter the rows into the separate divisions). Now, I know I can invoke fxFilterRows for each division to separately get the needed subtables, but it seems like there should be an easier way (vba?) to pass the division names in and have this happen automatically (similar to the pivot table that can create subpivots on based on a filter).

fxCn&CleanData

Please Login or Register  to view this content.

fxFilteredRows

Please Login or Register  to view this content.

My other issue is how to reference the output of a function in PQ. When invoked in the PQ Editor, the resulting table name is "Invoked FunctionfxCn&CleanData" unless I rename it inside the PQ editor (seems you can't do with VBA?). Also, using "Invoked FunctionfxCn&CleanData" isn't recognized as a valid table.

**If there's an easier way to get the resulting workbooks from the original file without having to do the PQ functions, I'm all for it, but our machines have memory issues trying to open the original files, which is why I was trying to use PQ.