Forum Discussion

Ainala's avatar
Ainala
Frequent Visitor
4 years ago

Excel Column Changes in Power query

Hello Power Users,

I'm trying to work on an excel data and i'm loading the excel to Power BI and I would like to change the column name every month. Example data looks like below.

When the month changes the current month should change to Past. In August we have remove past month column and rename the Jul-22 to Past and in September we have remove past month column and rename the Aug-22 to Past and so on. How can i achieve this and also we are going to add future months data aswell.  How can i acheive this? 

 

Please help.

 

Thanks,

 

 

Need Help

4 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Ainala ;

    You could unpivot in power query.

    Then in desktop create a column by dax.

    Column = IF(EOMONTH([year-mon],0)<EOMONTH(TODAY(),0),"Past",FORMAT([year-mon],"mmm-yy"))

    then add a sort column.

    sort = IF(EOMONTH([year-mon],0)<EOMONTH(TODAY(),0),1,RANKX(ALL('Table'),[year-mon],,ASC,Dense)+1)

     


    add a matrix visual.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Ainala's avatar
      Ainala
      Frequent Visitor
      4 years ago

      v-yalanwu-msft I have do them in Power query itself. The data changes in the excel and the power bi data load would break because of the column changes. is there a way to do this in Power query and filter them out. 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi Ainala ,

    I don't quite understand what you mean, what do you mean by interrupt and filter out? Can you share your simple file?


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Ainala ;

    Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.