Grouping non-numerical rows

[click-here]

I have the following data structure:

My query ensures that there is never more than two rows for each person as there will only ever be 2 or 1 timestamp and they will always be offset like the above example.

How can I obtain the bottom output?

[erik_tarnvik · Accepted solution]

It is not clear from you question if this is something you are trying to do in the query editor or if you are trying to do this in DAX. Assuming you are trying to transform the data into your desired shape prior to modeling (i.e. using the query editor), here are steps that will lead you to the desired result.

First, in the query editor, select column "Things 1" and "Things 2". Then click "Unpivot Columns" in the Transform menu.

Second, filter out all rows with the value "null" in the resulting "Value" column. 

Third, select the "Attributes" column and press "Pivot Column" in the Transform menu. In the resulting dialogue box, select "Value" for Values Column, press "Advanced Options" and select "Don't aggregate".

Here is the resulting M code:

let
    Source = Excel.Workbook(File.Contents("C:\testdata.xlsx"), null, true),
    Times_Sheet = Source{[Item="Times",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Times_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "timestamp")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

[click-here]

Brilliant, thank you, this is exactly what I was looking for!

[Phil_Seamark]

Hi @click-here

You could try creating a calculated table using the following code.  This groups down to 1 line per person and picks the MIN (you could use MAX if you prefer) value.  

Just replace the three instances of Table5 from this code with the name of your table.

New Table = SUMMARIZECOLUMNS(
                        'Table5'[Name],
                        "Thing 1" , MIN('Table5'[Thing1]) ,
                        "Thing 2" , MIN('Table5'[Thing2]) 
                        )