Help reshaping timeline data to go from [2 Rows by 1 Column] to [1 Row and 2 Columns]

[Anonymous]

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,

[mahoneypat · Accepted solution]

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

[Anonymous]

Thank you Pat, that worked perfectly.  Appreciate your code/example as it helped me step through the solution and learn something new!