Power Query Pivot Returns Integer Value Not Original String

[UncleLewis]

Hi all,

Using Excel 365.
I imported data from Table into Power Query, UnPivoted, Replaced Column Names so now I only have 2 Names in Attributes and various Values in Values Column.

But when I Pivot, PQ returns 3 in all cells as value not the orignal values which were either strings or 10 digit numbers.

What am I missing?

I uploaded the workbook to OneDrive: https://tinyurl.com/xf4kmhvk

Thanks,

-w

[HotChilli]

I'm going to make some assumptions because the question lacks a bit of detail so please review my response and get back to me.

You're starting with Sheet1 and you want a table with columns for Code and PO and you want to preserve the pairs indicated by the suffixes in the original table.

So, before the Unpivot rename column Code as Code1 and PO# as PO1.

Do the Unpivot.

Split the Attribute column (by Position, 1 character, as far right as possible - all done from the interface).  This preserves the row number 'pairing'.

Change the Value column to Text.

Pivot the Attribute1 column, using Value as Values (choose  Don't Aggregate in the Advanced).

Is that what you want?

[Syndicate_Admin]

I have to rename the fields after the Unpivot - correct?

Power Query will not allow me to rename the columns and give the same name.

Beginning column names:

• Code
• PO#
• Code2
• PO2
• Code3
• PO3
  
  

Thanks,

-w

[UncleLewis]

I have to rename the fields after the Unpivot - correct?

Power Query will not allow me to rename the columns and give the same name.

Beginning column names:

• Code
• PO#
• Code2
• PO2
• Code3
• PO3
  
  

Thanks,

-w

[HotChilli]

Er, no.

"So, before the Unpivot rename column Code as Code1 and PO# as PO1.

Do the Unpivot."

[UncleLewis · Accepted solution]

Thanks HotChilli,

I'll take a look at that, In the meantime, solved the problem using Conditional Column and a few additional steps. Solution using Conditional Column is here : https://tinyurl.com/4nd6rny4

Thanks,

-w