Create flattened table from logtable

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

Need Help

lbendlin

2 years ago

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.

6 Replies

AlienSx

Super User

2 years ago

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

lbendlin

Super User

2 years ago

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"

PQ-Noob675137
Frequent Visitor
2 years ago
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
  Super User
  2 years ago
  what for? You should avoid sorting in Power Query as much as possible, it is very expensive.

PQ-Noob675137
Frequent Visitor
2 years ago
Ive just seen ur message and havent tried, but thanks for the help. I will come back at you tommorow!

Forum Discussion

Create flattened table from logtable

6 Replies

Related content

Flatten nested JSON in KQL DB

Null values in flattened JSON

Power Query - flatten Parent-child hierarchy

Creating ICMs via Activator

Creating relationships