Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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.
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.
Dears !
thank you very much !! it works perfectly.
Have a nice day
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
40 | |
30 | |
26 | |
19 | |
17 |