How can I create a PivotTable with multiple rows?

[alee023]

I'm using Dataflow Gen2 in Fabric Data Engineering/Data Factory. Under the "Transform" tab, I see the option to "Pivot column" if I have a column selected. How can I replicate Excel's PivotTable functionality where I can select multiple columns to put under "Rows"? See example below where I have Environment and Severity as Rows

For example, if I have the data below:

Environment	Account	Severity	Status
prod	000	High	Fixed
prod	001	Low	New
nprod	006	Medium	Reopened
nprod	003	Medium	Fixed

Then I want the PivotTable to look like the below. In Excel, the selections are - Columns: Status, Rows: Environment + Severity, Values: Count of Accounts

Count of Accounts	Column Labels
Row Labels	Fixed	New	Reopened	Grand Total
prod	1	1	0	2
High	1
Low		1
nprod	1	0	1	2
Medium	1		1

[miguel · Accepted solution]

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

[alee023]

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.

[Anonymous]

Hi @alee023 
Thanks for using Fabric Community.
You can create a pivot table similar to excel pivot table as follows:

Here is the code:

 I have used a custom function and achieved this. If you want you can delete the Attribute column.
Hope this helps. Please let me know if you have any further questions.

[Anonymous]

Hi @alee023 
We haven’t heard from you on the last response and was just checking back to see if you query has been resolved. Please let us know if you have any further queries. Glad to help.
Thanks

[Anonymous]

Hi @alee023 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.

Thanks.