cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Unpivoting Variable Number of Columns

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

edhans_0-1600466063325.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Unpivoting Variable Number of Columns

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

edhans_0-1600466063325.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
New Member

Re: Unpivoting Variable Number of Columns

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

Highlighted
Super User III
Super User III

Re: Unpivoting Variable Number of Columns

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors