topic Re: Problem with "pivote column" function in Power Query

Problem with "pivote column" function

Automater — Wed, 24 May 2023 14:29:14 GMT

Hello everybody,

I have following problem:
I depivote a table in power query going from this:

to this:

because I need to "translate" the values in the Column "Wert" with a second table. I translate those values and I end up with a table looking like this (only two columns):

The only thing, as I thought, would be pivoting the table again, using the column "Benutzerdefiniert" as the value column. As the aggregation method I chose "do not aggregate" as I want all values back in a table form, looking like the first screenshot just with translated values in the columns.

But everytime it gives me the error:

Expression.Error: There weren't enough elements in the enumeration to complete the operation

And from my understanding with the MS documentation this should be possible:

I hope I described my problem well enough, and I'm open for all your tips.

Thanks!

Max

Re: Problem with "pivote column" function

BA_Pete — Wed, 24 May 2023 15:10:16 GMT

Hi Max,

Try going to the Add Column tab > Index Column (dropdown) > From 1 BEFORE your last pivot step.

This should give Power Query an ID to use in order to reallocate values correctly to columns.

Pete

Re: Problem with "pivote column" function

Automater — Wed, 24 May 2023 15:29:37 GMT

Hi Pete,
thanks for your answer. I tried it and the output looked like this:

The problem here is, that for example the values of line 1,2,3 and 4 should be in one line, here they're splitted.

Re: Problem with "pivote column" function

BA_Pete — Wed, 24 May 2023 15:39:00 GMT

Ok. Try dragging and dropping the Add Index step up your steps list to before your first Unpivot.

This should give you ID groups after the unpivot that PQ will group back up correctly after the final pivot.

Pete

Re: Problem with "pivote column" function

Automater — Wed, 24 May 2023 17:21:08 GMT

Tried it too.

I added an index column before the first unpivot:

And after the unpivot step it looks like this:

But if I want to pivot it back again I still receive the same error...

Do you have any other tips?

Thank you so much

Max

Re: Problem with "pivote column" function

BA_Pete — Thu, 25 May 2023 05:42:43 GMT

It looks like you've unpivoted the Index with the other columns.

Select your index column before the unpivot step, go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns. This should keep your Index column intact to be used as an ID for the pivot step later.

If this doesn't work, can you provide your initial data from the following screenshot as copyable data please?

You can copy it from Excel and paste it directly in here, or you can paste it into 'Enter Data' in Power Query and copy the M code from that query.

Pete