Forum Discussion
Solved
Transposing data to new table
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
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"
1 Reply
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"