Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
patyk
Frequent Visitor

pivoting table

 I would require to pivot the following table

Capture.PNG

 

On Power BI page I would need to see data as follows:

 

From Date extract Year Month  for example Aug-18, Sept-18, Oct-18, Nov.-18, Dec-18, Jan-19

There is always 12 months of data I am showing however the date is fluctuating.  I could start from Aug or Sept or any other month.  It should sort date ascending.

Qty is total per month per product.

Here is an  example on how it should look like in a table, I am not sure on how to setup the Pivot ? and which visualization tool I should use.  Thanks

 

 

Capture1.PNG

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @patyk

If your purpose for pivoting table is just to show a visual like your screenshot, you could use the Matrix chart.

Before add columns to the Field of Matrix, you need to sort the "date" column by ascending, then change the date format to "MMMM yyyy".

11.png

 

If you need to pivot the table to change its structure, you could go to Edit queries

1. sort the date column

2.select "date" column, "pivot column"

14.png

3.replace values

replace "null" with 0

16.png

 

 

 

Best Reagrds

Maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @patyk

If your purpose for pivoting table is just to show a visual like your screenshot, you could use the Matrix chart.

Before add columns to the Field of Matrix, you need to sort the "date" column by ascending, then change the date format to "MMMM yyyy".

11.png

 

If you need to pivot the table to change its structure, you could go to Edit queries

1. sort the date column

2.select "date" column, "pivot column"

14.png

3.replace values

replace "null" with 0

16.png

 

 

 

Best Reagrds

Maggie

Sorry I can't really use matrix because when exporting to Excell it does not export in pivot format.  Needs to export to excell in the exact view?

 

Thanks

Hi @patyk

For your requirement, you could consider my sencond method, this would change the table structure, then you just add these columns to a Table visual, then you could export it to excel with the structure as you expected.

 

Best Regards

Maggie

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.