Power BI Pivot Columns ?

[Jasmine07]

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.

[cengizhanarslan · Accepted solution]

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]

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!