Forum Discussion
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?
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
13 Replies
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!
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:
Hi Anonymous, 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.
