Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |