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.
daHi,
I have a table that looks like this:
Division | Report Month | Incidents (current) | Incidents (Past) | Reports (currents) | Reports (past) |
Division 1 | 1/10/2021 | 10 | 9 | 8 | 10 |
Division 2 | 1/10/2021 | 12 | 4 | 5 | 3 |
I would like to transpose the data to create a new table that looks like the one below but can't work out how to get it working with Dax. Any suggestions?
Stat | Current | Past | Division | Report Month |
Incidents | 10 | 9 | Division 1 | 1/10/2021 |
Reports | 8 | 10 | Division 1 | 1/10/2021 |
Incidents | 12 | 4 | Division 2 | 1/10/2021 |
Reports | 5 | 3 | Division 2 | 1/10/2021 |
Thanks
Rob
Solved! Go to Solution.
Make sure that your names in columns are Matched and case sensitive...
eg. current & currents are 2 different
eg 2. Past & past are 2 different.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsksyyzOzM9TMFTSUTLUNzTQNzIwArMNgIQlEFtAOLE6SIqN0BWDBEyA2BSIjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, #"Report Month" = _t, #"Incidents (current)" = _t, #"Incidents (past)" = _t, #"Reports (current)" = _t, #"Reports (past)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", type text}, {"Report Month", type date}, {"Incidents (current)", Int64.Type}, {"Incidents (past)", Int64.Type}, {"Reports (current)", Int64.Type}, {"Reports (past)", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Division", "Report Month"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Stat"}})
in
#"Renamed Columns"
Proud to be a Super User!
Make sure that your names in columns are Matched and case sensitive...
eg. current & currents are 2 different
eg 2. Past & past are 2 different.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsksyyzOzM9TMFTSUTLUNzTQNzIwArMNgIQlEFtAOLE6SIqN0BWDBEyA2BSIjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, #"Report Month" = _t, #"Incidents (current)" = _t, #"Incidents (past)" = _t, #"Reports (current)" = _t, #"Reports (past)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", type text}, {"Report Month", type date}, {"Incidents (current)", Int64.Type}, {"Incidents (past)", Int64.Type}, {"Reports (current)", Int64.Type}, {"Reports (past)", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Division", "Report Month"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Stat"}})
in
#"Renamed Columns"
Proud to be a Super User!
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 |