Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
islandtime
Frequent Visitor

Transform an excel table with nested data

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:Capture.JPG

 

 

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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):

 

Transform an excel table with nested data.png

 

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"
Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug
Community Champion
Community Champion

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):

 

Transform an excel table with nested data.png

 

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"
Specializing in Power Query Formula Language (M)

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors