Complex data transformation with dates and workload

[AT_jda_powerbi]

Dears,

Firstable thank you 😉

I'm facing a difficulty for the file that i need to treat when importing in Powerbi. 

The context is the following : i need to create some KPI based on input excel containing the team workload. The input files are excel file which have these columns :

- A --> C : name and role of the team member 

- D --> : Project on which the team member will work (if the team member works on several project then there will be several lines)

- E ---> H (and more)  : workload by month depending of the project 

Example for John Doe :

header : Name|Surname|Role|Projects|09/2022|10/2022|11/2022 ... 

Line 2   : John   |Doe        |Arch|A          |0.2        |0.3         |0.5

Line 3   : John   |Doe        |Arch|B          |0.8        |0.7         |0.5

I receive 4 differents file that i need to concatenate. This is can do with PowerBI directly (and automatically). However, directly from powerbi i cannot manage the data in the table (dates are not recognized). I need to transformed the file (in VBA) before importing it with the follwoing structure :

header : Name  |Surname|Role|Projects|Dates        |Workload .

then for the previous example 

Line 2   : John   |Doe        |Arch|A         |09/2022   |0.2       

Line 3   : John   |Doe        |Arch|A         |10/2022   |0.3       

Line 4   : John   |Doe        |Arch|A         |11/2022   |0.5

Line 5   : John   |Doe        |Arch|B         |09/2022   |0.8        

Line 6   : John   |Doe        |Arch|B         |10/2022   |0.7        

Line 7   : John   |Doe        |Arch|B         |11/2022   |0.5

My question is the following : it it possible to do the same with powerquery ? and if yes, what could be the principle ? It is difficult to manage and excel vba and powerbi for automatic task.

Thanks

Julien

[artemus · Accepted solution]

In power query you would select all the non-date columns, and choose Transform -> Unpivot Columns -> Unpivot other columns. This will replace the date columns with an Attribute column and a Value column. You would rename these to Dates and Workload respectivly. Then you can convert the date column to the date type.

[v-yalanwu-msft · Accepted solution]

Hi, @AT_jda_powerbi ;

As artemus said, You could select those column then unpivot.

Then change the column's type and name.

The final show:

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.

[artemus · Accepted solution]

In power query you would select all the non-date columns, and choose Transform -> Unpivot Columns -> Unpivot other columns. This will replace the date columns with an Attribute column and a Value column. You would rename these to Dates and Workload respectivly. Then you can convert the date column to the date type.

[AT_jda_powerbi]

Dears ! 

thank you very much !! it works perfectly. 

Have a nice day