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 everybody,
I would like to transform the following table:
Contracte | Tipus contracte | Contractació any 2017 | Adjudicat any 2017 | Contractació any 2018 | Adjudicat any 2018 |
1 | A | 1000 | 2000 | 4000 | 2500 |
2 | B | 5600 | 2400 | 3500 | 5500 |
Into the following:
Contracte | Tipus contracte | Estat | Any | Import |
1 | A | Contractació | 2017 | 1000 |
1 | A | Adjudicat | 2017 | 2000 |
1 | A | Contractació | 2018 | 4000 |
1 | A | Adjudicat | 2018 | 2500 |
2 | B | Contractació | 2017 | 5600 |
2 | B | Adjudicat | 2017 | 2400 |
2 | B | Contractació | 2018 | 3500 |
2 | B | Adjudicat | 2018 | 5500 |
Could anyone give me a hand please?
Thank you very much in advance!
Solved! Go to Solution.
Select the first 2 columns, unpivot other columns.
Adjust the generated code with regard to the names of the new columns.
Split the Estat column with custom delimiter " any ".
Adjust both lines of the generated code with regard to the names of the new columns.
let Source = Table3, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Contracte", "Tipus contracte"}, "Estat", "Import"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Estat", Splitter.SplitTextByDelimiter(" any ", QuoteStyle.Csv), {"Estat", "Any"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Estat", type text}, {"Any", Int64.Type}}) in #"Changed Type"
Select the first 2 columns, unpivot other columns.
Adjust the generated code with regard to the names of the new columns.
Split the Estat column with custom delimiter " any ".
Adjust both lines of the generated code with regard to the names of the new columns.
let Source = Table3, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Contracte", "Tipus contracte"}, "Estat", "Import"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Estat", Splitter.SplitTextByDelimiter(" any ", QuoteStyle.Csv), {"Estat", "Any"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Estat", type text}, {"Any", Int64.Type}}) in #"Changed Type"
Thank you very much!
It certainly works!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |