Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Number of Columns Increasing With Each Month

I think I have a pretty unique problem where for each line item there will be associated data in a month column. However each month, the dataset will update with a new column for the new month

 

Ex. - Month 1

ID10/01/2021
1.37
2.84

 

Ex. - Month 2

ID10/01/202111/01/2021
1.37.14
2.84.50

 

Question - is there a way to manage the increasing number of columns each month in PQ?

 

What I've tried - I tried a grouping on the ID, but this won't work in the new months.

 

Any Ideas?

Need Help
  • edhans's avatar
    edhans
    4 years ago

    Yes. Select the ID Column, then right-click and "Unpivot Other Columns"

    Now you can have unlimited date columns and they will be in one column and your values in another. This is the proper way to build a table. The Unpivot feature is one of the best things about Power Query.

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    Yes. Select the ID Column, then right-click and "Unpivot Other Columns"

    Now you can have unlimited date columns and they will be in one column and your values in another. This is the proper way to build a table. The Unpivot feature is one of the best things about Power Query.

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    Have you tried edhans 's solution? It should work well. Let us know if you have any questions. 

     

    Best Regards,
    Community Support Team _ Jing