Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to transform a table which is:
Client | Estat | Any | Import |
EU | Línea base | 2017 | 10000 |
EU | Adjudicat | 2017 | 3000 |
EU | En contractació | 2017 | 2000 |
EU | En contractació | 2018 | 5000 |
EU | Adjudicat | 2018 | 10000 |
EU | Línea base | 2018 | 20000 |
And I would like to transform it, so that I had a new "Estat" called "Pendent" with the calculation "Pendent" = "Línea base" - "Adjudicat" - "En contractació". The resulting table would be:
Client | Estat | Any | Import |
EU | Pendent | 2017 | 5000 |
EU | Adjudicat | 2017 | 3000 |
EU | En contractació | 2017 | 2000 |
EU | En contractació | 2018 | 5000 |
EU | Adjudicat | 2018 | 10000 |
EU | Pendent | 2018 | 5000 |
This transformation would enable me to represent a stacked column chart I am looking for.
Could anyone shed a light please?
Thank you in advance!
Àlex
Solved! Go to Solution.
The suggested (un)pivot solution could look like this:
let Source = Table1, #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Estat]), "Estat", "Import"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Pendent", each [Línea base] - [Adjudicat] - [En contractació], Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Línea base"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Client", "Any", "Pendent", "Adjudicat", "En contractació"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Client", "Any"}, "Attribute", "Value") in #"Unpivoted Columns"
Hi @pect,
Could you please mark the proper answer as solution? That will be a big help to the others.
Best Regards!
Dale
Hi @pect,
go to the query Editor:
and add a "Custom Column"
Than you can craete a new, custom column with the formular you stated:
Hi,
Thanks but I am afraid this doesn't work with my request. My calculation depends on only one column "Import".
I appreciate any other help you could provide me!
Thank you very much.
Now I understand!
Does this help you`?
How do I join the 3 tables?
Joining was the wrong word, in Power BI its called "Appending".
Click on the black arrow next to it and use "append as new"
The suggested (un)pivot solution could look like this:
let Source = Table1, #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Estat]), "Estat", "Import"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Pendent", each [Línea base] - [Adjudicat] - [En contractació], Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Línea base"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Client", "Any", "Pendent", "Adjudicat", "En contractació"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Client", "Any"}, "Attribute", "Value") in #"Unpivoted Columns"
Thank you very much! It works!
Alternative: (you have to try this, i´m not exactly sure)
fondle around with "unpivot column" or "pivot column"