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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Maahmohammed
Frequent Visitor

Pivot

Upon using Matrix to develop a pivot table, how could I save this pivot table as a new table in my Power BI data base?

At the same time, when I select 2 fields in the row segment, they become one over the other, how could they be adjacent to each other (i.e., to appear in 2 separate columns in the pivot table) as we did before in Excel (tabular)?

Regards

1 ACCEPTED SOLUTION

Hi @Maahmohammed ,

 

When you do a pivot in Power BI, there are really only two main dimensions: columns and values. This is different from the pivot table in Excel, which has three dimensions: rows, columns, and values. This difference is mainly due to the different design concepts and usage scenarios of Power BI and Excel.

 

For the first problem, you can just export the data of the original matrix in Power BI desktop:

vlinhuizhmsft_0-1722844959978.png

Then import the resulting excel file into desktop again.

 

vlinhuizhmsft_1-1722844959979.png

 

If I misunderstood you, please provide your data, screenshots and expected results so that your problem can be better solved.

 

Best Regards,
Zhu
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

3 REPLIES 3
Maahmohammed
Frequent Visitor

Appreciating

For 2nd question, it's ok

For the 1st question (Pivot), the design of the option (Pivot column) is limited as per the below details:

1 - Only 2 dimensions (Columns & values), there was no rows as excel.

2 - it's in an oppoit structure as the field that i pivoted will be the header of columns but we need to be the 1st column.

3 - No possibility to make oulined table e.g. (Item code - Item Name) then we could aggregate other results (Sum of Q - Sum of Slaes, etc) as in excel

Hi @Maahmohammed ,

 

When you do a pivot in Power BI, there are really only two main dimensions: columns and values. This is different from the pivot table in Excel, which has three dimensions: rows, columns, and values. This difference is mainly due to the different design concepts and usage scenarios of Power BI and Excel.

 

For the first problem, you can just export the data of the original matrix in Power BI desktop:

vlinhuizhmsft_0-1722844959978.png

Then import the resulting excel file into desktop again.

 

vlinhuizhmsft_1-1722844959979.png

 

If I misunderstood you, please provide your data, screenshots and expected results so that your problem can be better solved.

 

Best Regards,
Zhu
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

v-linhuizh-msft
Community Support
Community Support

Hi @Maahmohammed ,

 

Based on your description, I created the simple matrix:

vlinhuizhmsft_1-1722568677533.png

For your first question, here is the solution in Power Query Editor:

 

1.Duplicate the original table:

 

vlinhuizhmsft_3-1722568778204.png

 

2.Select the fields used in the matrix and remove other columns:

 

vlinhuizhmsft_4-1722568825408.png

 

3.Pivot the Year column and you can get the desired outcome:

 

vlinhuizhmsft_5-1722568860252.png

 

vlinhuizhmsft_6-1722568887282.png

4.Apply the steps and get a new table:

 

vlinhuizhmsft_7-1722568906061.png

 

Regarding the second question, my understanding is to show the subcategories in the hierarchy below each parent in a separate column. Is this consistent with the results you want?

 

It can be set in the visualizations pane:

 

vlinhuizhmsft_8-1722568926718.png

 

The effect is shown below:

 

vlinhuizhmsft_9-1722568949252.png

 

Best Regards,
Zhu
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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