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
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.
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
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.