Forum Discussion
Help reshaping timeline data to go from [2 Rows by 1 Column] to [1 Row and 2 Columns]
Hi All, I'm a little over my head and could use a hand trying to take timeline information and reshape it (ultimately for a gant chart visual). My data comes in as follows [Attribute | Date V...
Here is an example on how to do this. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEILkksKlHSUTLUN9Q3MjAyUIrVQUi65qWApYwRUs75ecUlRaXJJZn5eXDNRjhVQEww0jeByscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", type date}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "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}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value") in #"Pivoted Column"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat
Microsoft Employee
Microsoft EmployeeHere is an example on how to do this. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEILkksKlHSUTLUN9Q3MjAyUIrVQUi65qWApYwRUs75ecUlRaXJJZn5eXDNRjhVQEww0jeByscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", type date}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "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}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thank you Pat, that worked perfectly. Appreciate your code/example as it helped me step through the solution and learn something new!