Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[John Fjord]

I often have a problem when I need to pivot data in the Query editor. It seems to be a very basic task, but the “correct” solution eludes me everytime. I hope someone could show me a form of general solution to my problem.

I have 2 columns, let’s say a ‘category’ and a ‘value’.

Cat.	Value
A	1
B	2
C	3
A	4
B	5
C	6
A	7
B	8
C	9

And the result should look like

A	B	C
1	2	3
4	5	6
7	8	9

Thanks,

——————————
John Fjord
Instructor, consultant
XlHaj
Lyngby, Denmark
+45 3168 0770
——————————

[Sam Chatterjee]

Hey John

Go to the transform tab and open pivot column under that use value column and aggregate value function or you can choose your own to get the desired result.

sam

——————————
Sam Chatterjee
Business Intelligence Developer
Sam Analytiks
(M):-(+45) 9163 4228
URL:- https://www.samanalytiks.com
——————————
——————————————-

[John Fjord]

Thanks Sam,

But that is exactly my problem, I do not want to aggregate “Value”. So if I choose “Do not aggregate” under advanced, I get a table like this:

A	B	C
Error	Error	Error

This error being: “Expression.Error: There were too many elements in the enumeration to complete the operation.”

/John

——————————
John Fjord
Instructor
XLhaj
Lyngby
31680770
——————————
——————————————-

[Doug Beardmore]

?Hi John,

Try this.

In the Add Column tab, select Index column, starting at 0
Then select Conditional column with the new column name “Row ID”, if Column Name “Cat.”, Operator equals, Value box type in A, change the Output type to Select a Column and choose Index. In the Otherwise box type in null. Click Ok.
Click the Row ID column, from the Transform tab, Fill Down.
Right click the Index column and Remove it.
Click on the Cat. column and click Pivot Column, leave Values column Value, in the Advanced options select Don’t aggregate. Click Ok.
Right click the Row ID column and Remove it.

Doug

h/t Gil Raviv’s  “Collect, Combine, and Transform Data Using Power Query in Excel and Power BI”, pages 177-179.

——————————
Doug Beardmore
Quality Manager
Janesville WI
608-755-1495
——————————
——————————————-

[John Fjord]

Hi Doug,

Brilliant, I knew there had to be someone who had the same challenge. Thank you very much. You saved my day.

——————————
John Fjord
——————————
——————————————-

[Doug Beardmore]

?You’re quite welcome John.
I’m glad I could help!

Doug

——————————
Doug Beardmore
Quality Manager
Janesville WI
608-755-1495
——————————
——————————————-