Forum Discussion

DearestYoki's avatar
DearestYoki
Frequent Visitor
5 years ago

How to automatically unpivot columns with fixed position?

Hi all,

 

I am developing a dashboard using an raw file like this:

Part 1Part 2

It contains 3 portions:

1. Master attributes with black headers

2. Numeric values with green headers

3. Empty columns with default headers

The file is updated monthly, using the above snapshot as example, once August 2020 is completed, the JUL18 column will be removed, all following month columns are shifted 1 place to the left, hence column M becomes "AUG18" and column AK becomes "AUG20". The overall position of month columns is always fixed at M-AK.

In PBI, I am now manually unpivot all month columns into one single month column and delete all blank columns. Everytime when the file is updated, I need to manually delete the unpivoting step in power query and apply unpivoting again to the updated month columns.

I would like to ask whether there is an automatic way to do it. It can be using advanced query or any other tool.

 

Best Regards,

Yoki

Need Help

4 Replies

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

    DearestYoki ,

     

    Have you tried to use the option "Unpivot other columns" ? So you keep the static columns and unpivot the others.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    DearestYoki , are you loading a different file every time. Steps should repeat once defined on new data. or load the file from a folder then the same steps can be repeated

     

    • DearestYoki's avatar
      DearestYoki
      Frequent Visitor
      5 years ago

      Yes. Every updated file stands alone so I need to change path of source file as well.

      After changing path the columns will run into error and I need to manually do it again.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi DearestYoki ,

     

    If spreadsheet is going to have same number of columns and names just with poor data each time then in power bi desktop you can go to query editor, right click your original query and make duplicate of it. Then simply click on setting icon next to Source (first step in query) and point it to your new spreadsheet and click Refresh. This will repeat all those steps you applied previously. But in your case, your updated file contains different columns and names, so it will not work. I am afraid that you need to do it manually since your updated file stands alone.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai