Query a Monday extracted Excel with Sub-Items

[Nivio]

I have a board on Monday which includes some groups.

Each group has several parent-items and each parent item has sub-items.

I exported this board to Excel  - view screenshot that shows 2 tests, with 3 and 4 sub-items.

I'd like to use power query to organize the data so I can pivot and present the tests name with all it's sub-items and the steps results in a pivot table.

Is it possible and how to handle those sub-items?

[samratpbi]

Hi, First of all, I created an excel like yours as below:

Then I have done the below transformation:

let
Source = Excel.Workbook(File.Contents("C:\XXXX\XXX\XXXXXXX\PQ.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Module", type text}, {"Subitems", type text}, {"Item ID", type text}, {"Test Details", type text}, {"Run By", type text}, {"Run Outcome", type any}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Name", "Module"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Item ID] <> null) and ([Subitems] <> "Name"))
in
#"Filtered Rows"

in above replace xxx with your location.

and output is like below:

to sum up, 2 simple steps, first fill down Name & Module columns. Then Filter  Subitems <> Name & Item ID <> null.

If this helps to resolves your problem, then please mark it as solution, Thanks!

[Nivio]

Hi @samratpbi thanks for the effort.

Your solution is mixing up the data in the table. for example review your screenshot and see that under Run By colums appear the step result "Pass".

The solution requires to somehow to produce this kind of a table:

[dufoq3]

Hi @Nivio, provide sample data in usable format (if you don't know how - you can check Note below my post), or you can upload your excel file i.e. to google drive and provide a link with public permissions. Provide also expected result based on sample data.

[Nivio]

Sure

Here is a link to a sample file

https://docs.google.com/spreadsheets/d/1ugkJ6zXsSKOmZCCuuHNHm2mlEM2Mr2RQ/edit?usp=sharing&ouid=10735...

Thanks

[dufoq3]

What about expected result?

[Nivio]

I update the file under the link  above with a tab showing the required result.

Here is also a screenshot:

[dufoq3 · Accepted solution]

Like this?

Output table:

Output Pivot Report based on Output table

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\Example.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    FilteredRows = Table.SelectRows(Data_Sheet, each [Column1] <> null and not Text.StartsWith([Column1], "*")),
    RemovedTopRows = Table.Skip(FilteredRows, each [Column1] <> "Name"),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars=true]),
    RemovedOtherColumns = Table.SelectColumns(PromotedHeaders,{"Name", "Module Name", "Subitems", "Item ID", "Test Details", "Run By", "Run Outcome"}),
    FilteredRows2 = Table.SelectRows(RemovedOtherColumns, each ([Name] <> "Name")),
    AddedIndex = Table.AddIndexColumn(FilteredRows2, "Index", 0, 1, Int64.Type),
    Ad_TestName = Table.AddColumn(AddedIndex, "Headers", each 
        [ a = AddedIndex{[Index = [Index]+1]}?[Name]?,
          b = if a = "Subitems" then [Test Name=[Name], Module Name=[Module Name], Subitems=[Subitems], Item ID=[Item ID], Test Details = [Test Details], Run By = [Run By], Run Outcome = [Run Outcome]] else null
        ][b], type record),
    FilledDown = Table.FillDown(Ad_TestName,{"Headers"}),
    Ad_GroupHelper = Table.AddColumn(FilledDown, "GroupHelper", each [Headers][Test Name], type text),
    GroupedRows = Table.Group(Ad_GroupHelper, {"GroupHelper"}, {{"All", each
        [ a = Table.Skip(Table.Skip(_, (x)=> x[Item ID] <> "Expected Result")),
          b = Table.AddIndexColumn(a, "Number", 1, 1, Int64.Type),
          c = Table.SelectColumns(b, {"Headers", "Subitems", "Item ID", "Test Details", "Run By", "Number"})
        ][c], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    RenamedColumns = Table.RenameColumns(CombinedAll,{{"Item ID", "Expected Result"}, {"Test Details", "Actual Result"}, {"Run By", "Test Step Outcome"}, {"Subitems", "Step Name"}}),
    ExpandedHeaders = Table.ExpandRecordColumn(RenamedColumns, "Headers", {"Test Name", "Module Name", "Subitems", "Item ID", "Test Details", "Run By", "Run Outcome"}, {"Test Name", "Module Name", "Subitems", "Item ID", "Test Details", "Run By", "Run Outcome"})
in
    ExpandedHeaders

[Nivio]

Wow that's great, thank you so much.

Last missing thing is the Subitems column name which is required as a column at the final result

I tried adding it to the code but did not succeed.

Can you tell me how to do it or send me an updated code?

[dufoq3]

Sorry, I missed this one. Code above has beed updated. Check it now.

[Nivio]

Much appriciated!!!

I think this will be the use case for every one who'd like to execute query on Monday Excel export.

[dufoq3]

You're welcome Nivio.