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, when I select the columns in Power Query, the Pivot Columns option is disabled and I can only choose Unpivot Columns.

I’ve searched through Copilot and community posts for a solution, but when I copy the suggested M code into the Advanced Editor, I encounter a syntax error starting from the first line.

 

Could someone help me understand:

  • Why these columns cannot be pivoted in Power BI, and
  • What I might be doing wrong with the code or approach?

Thanks in advance for your help.

 

 

 

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

2 Replies

  • 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!

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    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).