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 Value]:
(whereby the row contains the start line and the end line)
Ultimately, I need to get rid of the multi-rows per "Attribute" and merge them to one row with two columns (start / end). I've created a couple of custom columns to extra start dates and end dates but I'm not sure how to merge my 'Attributes' to one row. Progress thus far:
Any tips on how I could modify the above to get rid of the "start and end" components of the rows and just flatten to one row per?
Example:
[Attribute] | [Start Date] | [End Date]
Construction | Start | End
Project | Start | End
Thanks for your time,
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
2 Replies
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