Transform an excel table with nested data

[islandtime]

Hi all,

I have a table which consists of inspection event details, and some inspections include nested information (actions raised during the inspections). Is there are way that I can clean this table or export the nested rows of actions into a related query, so that I can use the nested information?

The table currently looks like this:

Very new to this, so any help is greatly appreciated!

[MarcelBeug · Accepted solution]

Hi,

It would help if you also explain what the result should look like.

I assumed it shoud be something like this, with left MasterTable and right ActionTable (just a few rows):

Queries (you can copy and paste in the Advanced Query edtor, accessible via "Edit Query"):

Both queries are generated using standard menu options in the Query Editor.

MasterTable:

let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Table1, each ([ID] <> null))
in
    #"Filtered Rows"

ActionTable:

let
    Source = Table1,
    #"Filled Down" = Table.FillDown(Source,{"ID"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Method] = null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Site", "ActionID"}, {"Type", "Action Taken"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([ActionID] <> "ID")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Method", "Event Date"})
in
    #"Removed Columns"

[islandtime]

Of course! thanks so much. I was over-thinking it, when all I had to do was duplicate the table and filter accordingly.