Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
UncleLewis
Resolver III
Resolver III

Power Query Pivot Returns Integer Value Not Original String

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

1 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

View solution in original post

5 REPLIES 5
HotChilli
Super User
Super User

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?

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

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

Er, no.

 

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

Do the Unpivot."

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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors