Forum Discussion

radomzal's avatar
radomzal
New Member
5 years ago
Solved

Unpivoting Variable Number of Columns

Hello,

 

My dataset is an Excel file that is generated by a very inflexible program.  In order to make functional tables, I need to unpivot the columns.  My problem is that the number of columns in the Excel file changes every time the file is generated.

 

My data looks like this:

Date | Property | Product 1 | Product 2 | Product 3 .......

 

When I unpivot the Product columns the formula is:

= Table.Unpivot(#"Changed Type", {"Product 1", "Product 2", "Product 3", .... }

 

How can I change the function to unpivot all columns from column #3 up to (and including) the last column?

 

Thanks!

  • radomzal you can select the first two columns Date and Property, then Unpivot Other Columns.

    It doesn't matter if there is 1 or 100 other columns, they will all get unpivoted.

     

3 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion

    radomzal you can select the first two columns Date and Property, then Unpivot Other Columns.

    It doesn't matter if there is 1 or 100 other columns, they will all get unpivoted.

     

    • radomzal's avatar
      radomzal
      New Member

      I can't believe I missed noticing that was an option!  Thanks!

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion

        Glad to help radomzal  - I'd say 95% of the time I use the Unpivot Other Columns feature vs the Unpivot Columns feature. 😁