Forum Discussion

Jasmine07's avatar
Jasmine07
Regular Visitor
5 months ago
Solved

Power BI Pivot Columns ?

Hi all, I’m trying to pivot two columns in my dataset in Power BI. While retaining all values exactly as they are, even if they are repeated (i.e. no aggregation such as sum or average). However...
Need Help
Tutorial Requests
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    From your screenshot, your query already ends with:

     in
#"Renamed Columns2"
     

    Then you pasted another let ... in ... block underneath. Power Query can only have one let … in … per query. So you must either:

    • Replace the entire query with the new code, or

    Create a New Query and paste the code there.

    Seems like your problem lies in here.

     

    So instead of mixing the code in a wrong way try using the UI of power query. Also could help if you share your .pbix or full code.

    1. In Power Query Editor, select the column that should become the new column headers.
    2. Go to Transform → Pivot Column.
    3. Choose the column that contains the values.
    4. Select an aggregation (usually Sum, Max, or Don't Aggregate if each key is unique).
grazitti_sapna's avatar
grazitti_sapna
Super User
5 months ago

Hi Jasmine07,

 

Issue is that as there are multiple values against attributes, power BI doesn't recognise them, the best way is to create each value as unique

 

U can achieve it by adding an Index and group column in your table.

Adding index column Transform → Add Column → Index Column → From 0

then add a group column 

Add Column → Custom Column

enter this m code Number.IntegerDivide([Index],3)

 

U'll get output like this 

 

Attributes Values Index GroupID
A1 V1 0 0
A2 V2 1 0
A3 V3 2 0
A1 V4 3 1
A2 V5 4 1
A3 V6 5 1
A1 V7 6 2
A2 V8 7 2
A3 V9 8 2

 

Then pivot the columns, you'll get the final output like this

 

GroupID A1 A2 A3
0 V1 V2 V3
1 V4 V5 V6
2 V7 V8 V9

 

Now you can simply remove th group ID column.

 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!