Get Data From Excel Pivot Table

[Anonymous]

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.

[v-yiruan-msft]

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.

[lbendlin]

Please provide sample data in usable format.