Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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, wi...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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.

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    What about expected result?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

      Here is also a screenshot:

       

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        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