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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Nivio
New Member

Query a Monday extracted Excel with Sub-Items

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?

Nivio_0-1713460142686.png

 

1 ACCEPTED SOLUTION

Like this?

 

Output table:

dufoq3_0-1713701842822.png

 

Output Pivot Report based on Output table

dufoq3_1-1713701882789.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

11 REPLIES 11
samratpbi
Super User
Super User

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

samratpbi_0-1713479442232.png

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:

samratpbi_1-1713479683660.png

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!

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:

Nivio_0-1713681819190.png

 

 

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

What about expected result?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Here is also a screenshot:

Nivio_0-1713694321495.png

 

Like this?

 

Output table:

dufoq3_0-1713701842822.png

 

Output Pivot Report based on Output table

dufoq3_1-1713701882789.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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?

 

Nivio_0-1713707475894.png

 

Nivio_1-1713707565015.png

 

 

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Much appriciated!!!

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

You're welcome Nivio.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors