Forum Discussion
COMBINE TABLES AND COLUMNS
Firstly I would like to start this with saying I have a headache, a literally one. I have been asked to report on some data that, if I have my way, I would completely rewrite how they are record...
No point resorting to DAX for dataset transformation given that PQ handles it with ease,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJKUotLlYwVNJRMtM30zcyMIIwDY2Q2AYwtouLvq+vfiQQADlGhnCZWB2EUUZAKQhCUY1TaywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Address" = _t, #"Opt in Waiver Received Date" = _t, #"Opt in Review Date (Waiver plus 6 month)" = _t, #"WC 04/10/21" = _t, #"WC 11/10/21" = _t, #"WC 18/10/21" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opt in Waiver Received Date", type date}, {"Opt in Review Date (Waiver plus 6 month)", type date}}, "de"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Property Address", "Opt in Waiver Received Date", "Opt in Review Date (Waiver plus 6 month)"}, "Contact Due Week", "Contact Made"), Xform = Table.TransformColumns(#"Unpivoted Columns", {{"Contact Due Week", each Date.From(Text.Select(_, {"0".."9","/"}), "fr")}, {"Contact Made", each try Date.From(_, "fr") otherwise null}}) in Xform
CNENFRNL
Community Champion
Community ChampionNo point resorting to DAX for dataset transformation given that PQ handles it with ease,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJKUotLlYwVNJRMtM30zcyMIIwDY2Q2AYwtouLvq+vfiQQADlGhnCZWB2EUUZAKQhCUY1TaywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Address" = _t, #"Opt in Waiver Received Date" = _t, #"Opt in Review Date (Waiver plus 6 month)" = _t, #"WC 04/10/21" = _t, #"WC 11/10/21" = _t, #"WC 18/10/21" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Opt in Waiver Received Date", type date}, {"Opt in Review Date (Waiver plus 6 month)", type date}}, "de"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Property Address", "Opt in Waiver Received Date", "Opt in Review Date (Waiver plus 6 month)"}, "Contact Due Week", "Contact Made"),
Xform = Table.TransformColumns(#"Unpivoted Columns", {{"Contact Due Week", each Date.From(Text.Select(_, {"0".."9","/"}), "fr")}, {"Contact Made", each try Date.From(_, "fr") otherwise null}})
in
Xform