Transform an excel table with nested data

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"