Forum Discussion

UncleLewis's avatar
UncleLewis
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

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

Need Help
  • UncleLewis's avatar
    UncleLewis
    5 years ago

    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

5 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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?

    • UncleLewis's avatar
      UncleLewis
      Icon for Responsive Resident rankResponsive Resident
      5 years ago

      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

    • UncleLewis's avatar
      UncleLewis
      Icon for Responsive Resident rankResponsive Resident
      5 years ago

      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's avatar
        HotChilli
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Er, no.

         

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

        Do the Unpivot."

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      5 years ago

      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