Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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
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 @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.
Sure
Here is a link to a sample file
Thanks
I update the file under the link above with a tab showing the required result.
Here is also a screenshot:
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
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?
Much appriciated!!!
I think this will be the use case for every one who'd like to execute query on Monday Excel export.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
27 | |
16 | |
14 | |
14 |