topic Re: Query a Monday extracted Excel with Sub-Items in Power Query

Query a Monday extracted Excel with Sub-Items

Anonymous — Thu, 18 Apr 2024 17:15:54 GMT

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?

Re: Query a Monday extracted Excel with Sub-Items

samratpbi — Thu, 18 Apr 2024 22:38:40 GMT

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!

Re: Query a Monday extracted Excel with Sub-Items

Anonymous — Sun, 21 Apr 2024 06:43:50 GMT

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:

Re: Query a Monday extracted Excel with Sub-Items

dufoq3 — Sun, 21 Apr 2024 08:47:25 GMT

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.

Re: Query a Monday extracted Excel with Sub-Items

Anonymous — Sun, 21 Apr 2024 09:44:37 GMT

Sure

Here is a link to a sample file

https://docs.google.com/spreadsheets/d/1ugkJ6zXsSKOmZCCuuHNHm2mlEM2Mr2RQ/edit?usp=sharing&ouid=107358816008241097074&rtpof=true&sd=true

Thanks

Re: Query a Monday extracted Excel with Sub-Items

dufoq3 — Sun, 21 Apr 2024 10:02:14 GMT

What about expected result?

Re: Query a Monday extracted Excel with Sub-Items

Anonymous — Sun, 21 Apr 2024 10:13:04 GMT

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

Here is also a screenshot:

Re: Query a Monday extracted Excel with Sub-Items

dufoq3 — Sun, 21 Apr 2024 19:00:43 GMT

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

Re: Query a Monday extracted Excel with Sub-Items

Anonymous — Sun, 21 Apr 2024 13:53:57 GMT

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?

Re: Query a Monday extracted Excel with Sub-Items

dufoq3 — Sun, 21 Apr 2024 18:58:28 GMT

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

Re: Query a Monday extracted Excel with Sub-Items

Anonymous — Sun, 21 Apr 2024 19:09:29 GMT

Much appriciated!!!

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

Re: Query a Monday extracted Excel with Sub-Items

dufoq3 — Sun, 21 Apr 2024 19:23:15 GMT

You're welcome Nivio.

Re: Query a Monday extracted Excel with Sub-Items

asaprios — Mon, 03 Feb 2025 19:26:28 GMT

hi dufoq3 - I am having the same request and am wondering what I need to replace in your query in order for it to work with my file - can you help?

Name	Subitems	Lead	Project Type	Transaction Stage	Current RSF	Programmed RSF	LXD	Strategy	Critical Date (If Applicable)	Project Management Tracker	PjM Stage
Los Angeles, CA	Waiting on LL	AB	Renewal	3			2027-12-31	Renew
Subitems	Name	Responsible	Status	Date	Launch Strategy Info
	Waiting on LL lease draft
Chicago, IL	Legal reviewing	CD	Renewal	5	500	600	2027-02-28
Subitems	Name	Responsible	Status	Date	Launch Strategy Info
	Legal reviewing	EF	Working on it

Re: Query a Monday extracted Excel with Sub-Items

dufoq3 — Thu, 06 Feb 2025 10:21:49 GMT

Hi, how should the output looks like?