Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Get Data From Excel Pivot Table

Hi folks,

My source is a specific Excel pivot table exported automatically from MS Project visual reports. This pivot table have 2 hierarchical level in columns : Year and Month, as shown here. Unhappily enough, year takes a line, and month a second line.

 

When I import it into Power BI Desktop october release, I get columns, but years and months are still separated.

 

When I unpivot all months columns, I get a lot of "columnN" in the attribute column such as : 

 

What I did is to rename all columnN one by one with the corresponding month,

WHat i want is to get this (made with 10 replacements and a filter) :

 

How can I do to avoid manual renaming of columns and rename automatically columnN with MonthN (for instance Column6 = Janvier-2021)? I did it manually but this is not suited for repetitive use.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

    Could you please make some adjustments in the excel file just as below screenshot? Then you can import the excel file into Power BI Desktop after adjusted and make other transformations base on your requirement. Any comment or problem, please feel free to let me know.

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