Or, you can choose Connection Only, and check the box for "Load to Data Model" and you can create your pivot table from the data model. I would uncheck the "use original column name as prefix"įrom there click the Close and Load drop down menu top left, and choose Load To.Īt this point you can either choose to load all the combined data into a new worksheet, and the just create your pivot from there. If you click on that it will let you expand all of the nested tables. There is a button on the header for that column that looks kind of like a filter button but has 2 arrows. There is a column called Content where each row will just say "Table". You can apply filters if you don't really want every one, or leave it if you do. This will bring up a list of tables and/or named ranges in the current excel file. When the formula bar is visible, type in =Excel.CurrentWorkbook() In the power query editor you may need to go to view tab and check the box to show the formula bar. If it is not, you can still do it, but it is easier to create a new Excel file and connect power query in the new file to the existing file as a data source.Īssuming the data is in named ranges or tables, in the existing workbook, you can go to Get Data>Other Sources>Blank Query If the data on each worksheet is in a structured excel Table, or is a named range, you can do this with power query, load to data model, and then pivot on all of it with a pivot table.
0 Comments
Leave a Reply. |