Transposing data to new table

[rjtedge]

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

[FarhanAhmed · Accepted 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"