Forum Discussion
How can I create a PivotTable with multiple rows?
A Pivot Table has different layouts. Typically the "compact" view is something that goes beyond just a pivot table and goes into the visualization aspect. Power Query matches the "tabular view" layout that a pivot table can have in Excel.
To accomplish this, all you need to do is select the Status column and then do the Pivot transformation. In the Pivot dialog, select the advanced option and use the "Value column" as 'Account' and do a 'Count (all)' aggregation.
That will yield the result that you're expecting, except that the "grand total" is something that you need to calculate yourself. We do recommend using other tools, such as pivot tables or Power BI, for analytical purposes as Power Query is primarily aimed to be a Data Preparation tool.
Below is the documentation on how to pivot columns in Power Query:
Pivot columns - Power Query | Microsoft Learn
A Pivot Table has different layouts. Typically the "compact" view is something that goes beyond just a pivot table and goes into the visualization aspect. Power Query matches the "tabular view" layout that a pivot table can have in Excel.
To accomplish this, all you need to do is select the Status column and then do the Pivot transformation. In the Pivot dialog, select the advanced option and use the "Value column" as 'Account' and do a 'Count (all)' aggregation.
That will yield the result that you're expecting, except that the "grand total" is something that you need to calculate yourself. We do recommend using other tools, such as pivot tables or Power BI, for analytical purposes as Power Query is primarily aimed to be a Data Preparation tool.
Below is the documentation on how to pivot columns in Power Query:
Pivot columns - Power Query | Microsoft Learn
I did some research and it turns out I can create something similar to PivotTables in PowerBI, called a Matrix Visualization. I have a dataflow set up that transforms CSV data into usable data that gets used in creating a PivotTable (or Matrix Visualization). I'm using a dataflow because I have CSVs (using the same column formats) coming in every month and would like to automate the process in a data pipeline. Since the formats of my data coming out of the dataflow are the same every month, is there a way to insert the PowerBI transformation in the data pipeline?
For example, the pipeline, which I can trigger monthly, would look like the below. I already have the first 2 steps done.
Monthly CSV file lands in sharepoint folder -> DataFlow reads the data and transforms it into usable table -> PowerBI uses the data outputted by DataFlow and creates a Matrix Visualization.
