Create flattened table from logtable

[PQ-Noob675137]

Hi all,

I have some questions regarding Power query. I have a log table see first table below

Table 2 shows what im trying to achieve, with some example columnms. 

What i currently have is

- original table

- I created a reference table, which has the unique ID's. Next step would be to create the columns from table 2.

Can someone give me a direction of achieving this with M?

Thanks

[AlienSx]

let
    Source = table_01,
    f = (tbl) => 
    [a = Table.Sort(tbl, "Step"),
    green = List.PositionOf(a[colour], "Green", Occurrence.First),
    sit = List.PositionOf(a[action], "Sit", Occurrence.First),
    z = 
        [First_colour = List.First(a[colour]),
        last_colour = List.Last(a[colour]),
        Check for colour green = Byte.From(green <> -1),
        Process green = if green = -1 then 0 else a[Step]{green},
        Walk found = Byte.From(if sit = -1 then false else a[action]{sit + 1}? = "Walk")]][z],
    gr = Table.Group(Source, "ID", {"a", f}),
    exp = Table.ExpandRecordColumn(gr, "a", {"First_colour", "last_colour", "Check for colour green", "Process green", "Walk found"})
in
    exp

[PQ-Noob675137]

Ive just seen ur message and havent tried, but thanks for the help. I will come back at you tommorow!

[lbendlin · Accepted solution]

Your spelling is all over the place - not good in Power Query as that is case sensitive.

let
  fx = (A) =>
    let
      #"Added Custom" = Table.AddColumn(A, "First Color", each List.First(A[Color]), type text), 
      #"Added Custom1" = Table.AddColumn(#"Added Custom", "Last Color", each List.Last(A[Color])), 
      #"Added Custom2" = Table.AddColumn(
        #"Added Custom1", 
        "Green Present", 
        each if List.Contains(A[Color], "Green") then 1 else 0, 
        Int64.Type
      ), 
      #"Added Custom3" = Table.AddColumn(
        #"Added Custom2", 
        "Green Step", 
        each if [Green Present] = 1 then Table.SelectRows(A, each [Color] = "Green"){0}[Step] else 0, 
        Int64.Type
      ), 
      #"Added Custom4" = Table.AddColumn(
        #"Added Custom3", 
        "Walk after Sit", 
        each try
          
            let
              s = Table.SelectRows(A, each [Action] = "Sit"){0}[Step], 
              w = Table.SelectRows(A, each [Step] = s + 1){0}[Action]
            in
              if w = "Walk" then 1 else 0
        otherwise
          0, 
        Int64.Type
      ), 
      #"Removed Other Columns" = Table.SelectColumns(
        #"Added Custom4", 
        {"ID", "First Color", "Last Color", "Green Present", "Green Step", "Walk after Sit"}
      ), 
      #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"ID"})
    in
      #"Removed Duplicates", 
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WclTSUTIEYqec0lQgFZ6Yk60UqwMRNwLiyNScnPxyICM4swQuYQzE7kWpqXnoOkxAAhmZJRhGmQJxQGlRQQ6KjBPUcrglAUX5yanFxZl56XB5IwLyxkhWwtzoBHUKVn2xAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, Step = _t, Color = _t, Action = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"ID", type text}, {"Step", Int64.Type}, {"Color", type text}, {"Action", type text}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"ID"}, 
    {
      {
        "Rows", 
        each _, 
        type table [
          ID = nullable text, 
          Step = nullable number, 
          Color = nullable text, 
          Action = nullable text
        ]
      }
    }
  ), 
  #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fx([Rows])), 
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Added Custom", 
    "Custom", 
    {"First Color", "Last Color", "Green Present", "Green Step", "Walk after Sit"}, 
    {"First Color", "Last Color", "Green Present", "Green Step", "Walk after Sit"}
  ), 
  #"Removed Other Columns" = Table.SelectColumns(
    #"Expanded Custom", 
    {"ID", "First Color", "Last Color", "Green Present", "Green Step", "Walk after Sit"}
  )
in
  #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[PQ-Noob675137]

Hi, i have been able to transform your code and got it working.  It looks really nice and i can add multiple columns into your function.

I do have 1 more question, which i havent stated in my problem. Where do i perform a sort ASC by 'step'?

Thank you.

[lbendlin]

what for?  You should avoid sorting in Power Query as much as possible, it is very expensive.

[PQ-Noob675137]

Alright, in my example table the step is automaticly sorted asc. 
im unsure if this is the case with the actual data.