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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vsslasd1
Helper III
Helper III

PowerBI Pivot Question

I have the following Data in PowerBI:
pivot example.png

 

Two Questions:
How Do I pivot this data so that Categories are listed along the top, and the Col1, Col2, Col3, etc. are the row descriptions ? 
Also, I have another table which Describes Col1 as a different Description, such as PeriodNo, QuarterNo, etc. I'd like to use those Descriptions from another table instead of the actual Col1, Col2, Col3 Values. 

Here is my code so far, but I'm stuck:

WIP Summary =
var currentCategoryID = SELECTCOLUMNS('WIP-ALL',"Category",'WIP-ALL'[Category])

return
MAXX(
    FILTER(
CALCULATETABLE(
ADDCOLUMNS(
'WIP-ALL'[Col3],
'WIP-ALL'[Col4],
         "rank",
RANK.EQ(
'WIP-ALL'[Col3],
'WIP-ALL'[Col3], ASC
 
)
),
FILTER(
All('WIP-ALL'),
'WIP-ALL'[Category] = currentCategoryID
)
),
[rank] = 1
    ),
    [P1]
)
Thank you
 




1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@vsslasd1 clearly tells that you need to unpivot your raw table WIP-SA and WIP-SD, if for some reason you still need a pivoted table, you can create another table in the PQ that unpivot both these tables and append these together. 

 

It is a best practice and best modeling design to unpviot.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@vsslasd1 clearly tells that you need to unpivot your raw table WIP-SA and WIP-SD, if for some reason you still need a pivoted table, you can create another table in the PQ that unpivot both these tables and append these together. 

 

It is a best practice and best modeling design to unpviot.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I used transpose first, then made adjustments and then pivoted... Still new to this stuff

Thank you

parry2k
Super User
Super User

@vsslasd1 then you need to review that, the table you are using DAX, needs to be created as an unpivoted table.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

This is my Dax - and I'd like for it to be pivoted, and I need to be pivoted  in DAX

WIP-ALL =

UNION (
SELECTCOLUMNS(
FILTER('WIP-SD','WIP-SD'[Column1]<>1),
"Category", 'WIP-SD'[Column2],
"Territory", 'WIP-SD'[Territory],
"Col3", VALUE('WIP-SD'[Column3]),
"Col4", VALUE('WIP-SD'[Column4]),
"Col5", VALUE('WIP-SD'[Column5]),
"Col6", VALUE('WIP-SD'[Column6]),
"Col7", VALUE('WIP-SD'[Column7]),
"Col8", VALUE('WIP-SD'[Column8]),
"Col9", VALUE('WIP-SD'[Column9]),
"Col10", VALUE('WIP-SD'[Column10]),
"Col11", VALUE('WIP-SD'[Column11]),
"Col12", VALUE('WIP-SD'[Column12]),
"Col13", VALUE('WIP-SD'[Column13]),
"Col14", VALUE('WIP-SD'[Column14]),
"Col15", VALUE('WIP-SD'[Column15]),
"Col16", VALUE('WIP-SD'[Column16]),
"Col17", VALUE('WIP-SD'[Column17]),
"Col18", VALUE('WIP-SD'[Column18]),
"Col19", VALUE('WIP-SD'[Column19]),
"Col20", VALUE('WIP-SD'[Column20]),
"Col21", VALUE('WIP-SD'[Column21]),
"Col22", VALUE('WIP-SD'[Column22]),
"Col23", VALUE('WIP-SD'[Column23]),
"Col24", VALUE('WIP-SD'[Column24]),
"Col25", VALUE('WIP-SD'[Column25]),
"Col26", VALUE('WIP-SD'[Column26]),
"Col27", VALUE('WIP-SD'[Column27]),
"Col28", VALUE('WIP-SD'[Column28]),
"Col29", VALUE('WIP-SD'[Column29]),
"Col30", VALUE('WIP-SD'[Column30])),


SELECTCOLUMNS(
FILTER('WIP-SA','WIP-SA'[Column1]<>1),
"Category", 'WIP-SA'[Column2],
"Territory", 'WIP-SA'[Territory],
"Col3", VALUE('WIP-SA'[Column3]),
"Col4", VALUE('WIP-SA'[Column4]),
"Col5", VALUE('WIP-SA'[Column5]),
"Col6", VALUE('WIP-SA'[Column6]),
"Col7", VALUE('WIP-SA'[Column7]),
"Col8", VALUE('WIP-SA'[Column8]),
"Col9", VALUE('WIP-SA'[Column9]),
"Col10", VALUE('WIP-SA'[Column10]),
"Col11", VALUE('WIP-SA'[Column11]),
"Col12", VALUE('WIP-SA'[Column12]),
"Col13", VALUE('WIP-SA'[Column13]),
"Col14", VALUE('WIP-SA'[Column14]),
"Col15", VALUE('WIP-SA'[Column15]),
"Col16", VALUE('WIP-SA'[Column16]),
"Col17", VALUE('WIP-SA'[Column17]),
"Col18", VALUE('WIP-SA'[Column18]),
"Col19", VALUE('WIP-SA'[Column19]),
"Col20", VALUE('WIP-SA'[Column20]),
"Col21", VALUE('WIP-SA'[Column21]),
"Col22", VALUE('WIP-SA'[Column22]),
"Col23", VALUE('WIP-SA'[Column23]),
"Col24", VALUE('WIP-SA'[Column24]),
"Col25", VALUE('WIP-SA'[Column25]),
"Col26", VALUE('WIP-SA'[Column26]),
"Col27", VALUE('WIP-SA'[Column27]),
"Col28", VALUE('WIP-SA'[Column28]),
"Col29", VALUE('WIP-SA'[Column29]),
"Col30", VALUE('WIP-SA'[Column30])))
parry2k
Super User
Super User

@vsslasd1 best approach is to unpivot your data in PQ

 

- transform data
- select category column in your table
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add category column on rows,
- add attribute on columns
- add value on values section

To get the description from another table, set the relationship between these two tables on the attribute column and column from another table, assuming your 2nd table has a unique value and the relationship will be 1 to many.

 

On the matrix visual, you can now use the description from the 2nd table instead of using the attribute column, if you do all this right, everything will work.

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you - But that doesn't work - I need to do a DAX statement because my source table is derived from a DAX statement and doesn't offer the transpose function for DAX statement Tables 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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