none
How to display X number of rows beneath keyword? RRS feed

  • Question

  • Hello I am a power query noob. It has been very difficult to find information about how to use the M language in power query that I can understand. Can someone tell me if it is possible to find the rows underneath a row with a keyword? Using the data below. Lets say I loaded this in to power query, clicked the drop down arrow in the Sales column, selected filters-> contains $1,064.00.  Power query would then only display the row with Sales $1,064.00. What would the M code be to make power query find the row that contains $1,064.00 and a specified number of rows below it? I want power query to show me the row with $1,064.00 and the 5 rows beneath it for example regardless of what the sales value is. I know the answer is probably very simple, but I'm clueless when it comes to this M language.



    Last Name      Sales     Product Type     Company     Contact

    Smith             $1,675.00  EEE-312           Wok N Roll    Adams

    Johnson         $1,480.00   DC-1              Wok N Roll    Rogers

    Williams         $1,064.00   EE-2                Peace A PIzza Evans

    Jones             $1,390.00    DF-3               Kung Food     Webb

    Brown          $4,865.00    EEE-45            Peace A Pizza  Fields

    Williams        $1,243.00   FD-2              Kung Food     Mccoy

    Johnson        $9,339.00   DC-1              Kung Food    Hansen

    Smith            $1,891.00   EEE-312         Wok N Roll   Hamilton

    Jones            $9,213.00   FG-5              Wok N Roll    Woods
    Tuesday, March 10, 2020 6:16 PM

Answers

  • Eric

    Something more secure if there's no [Sales] = 1064 or if there's an overlap between 2 groups of rows. Still with data Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        targetSales = 1064,
        nbRowBeneath = 5,
        ChangedTypes = Table.TransformColumnTypes(Source,
            {{"Last Name", type text}, {"Sales", type number}, {"Product Type", type text}, {"Company", type text}}
        ),
        EmptyTable = #table(type table [Last Name=text, Sales=number, Product Type=text, Company=text],
            {{null,null,null,null}}
        ),
        posFirstTarget = List.PositionOf(ChangedTypes[Sales], targetSales, Occurrence.First),
        AddedIndex = Table.AddIndexColumn(
            Table.Skip(ChangedTypes, posFirstTarget),
            "Index", 0, 1
        ),
        SelectedTargetSales = Table.SelectRows(AddedIndex, each [Sales] = targetSales),
        AddedID = Table.AddIndexColumn(SelectedTargetSales, "ID", 1, 1),
        JoinedTables = Table.NestedJoin(AddedIndex,"Index", AddedID,"Index", "WithID", JoinKind.LeftOuter),
        ExpandedWithID = Table.ExpandTableColumn(JoinedTables, "WithID", {"ID"}, {"GroupId"}),
        FilledDownID = Table.FillDown(ExpandedWithID,{"GroupId"}),
        GroupedID = Table.Group(FilledDownID, {"GroupId"},
            {{"GrpID", each Table.FirstN(_, nbRowBeneath+1),
            type table [Last Name=text, Sales=number, Product Type=text, Company=text, Index=number, ID=number]
            }}
        ),
        ExpandedData = Table.ExpandTableColumn(GroupedID, "GrpID",
            {"Last Name", "Sales", "Product Type", "Company"}
        ),
        QueryOut = if posFirstTarget = -1
            then EmptyTable
            else ExpandedData
    in
        QueryOut

    Wednesday, March 11, 2020 8:10 AM

All replies

  • Hi Eric

    With Table.AddIndexColumn and Table.Range. Assuming data in Table1

    For Sales = 1064 and next 5 (if exist) rows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,
            {
                {"Last Name", type text}, {"Sales", Int64.Type}, {"Product Type", type text},
                {"Company", type text}, {"Contact", type text}
            }
        ),
        AddedIndex = Table.AddIndexColumn(ChangedTypes, "GroupId", 0, 1),
        IndexOfDesiredAmount = Table.SelectColumns(
            Table.SelectRows(AddedIndex, each [Sales] = 1064),
            "GroupId"
        ),
        AddedTableRange = Table.AddColumn(IndexOfDesiredAmount, "NextFiveRecords", each
            Table.Range(AddedIndex, [GroupId], 6), type table
        ),
        ExpandedTablesRange = Table.ExpandTableColumn(AddedTableRange, "NextFiveRecords",
        {"Last Name", "Sales", "Product Type", "Company", "Contact"}
        )
    in
        ExpandedTablesRange

    Tuesday, March 10, 2020 7:05 PM
  • Eric

    Something more secure if there's no [Sales] = 1064 or if there's an overlap between 2 groups of rows. Still with data Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        targetSales = 1064,
        nbRowBeneath = 5,
        ChangedTypes = Table.TransformColumnTypes(Source,
            {{"Last Name", type text}, {"Sales", type number}, {"Product Type", type text}, {"Company", type text}}
        ),
        EmptyTable = #table(type table [Last Name=text, Sales=number, Product Type=text, Company=text],
            {{null,null,null,null}}
        ),
        posFirstTarget = List.PositionOf(ChangedTypes[Sales], targetSales, Occurrence.First),
        AddedIndex = Table.AddIndexColumn(
            Table.Skip(ChangedTypes, posFirstTarget),
            "Index", 0, 1
        ),
        SelectedTargetSales = Table.SelectRows(AddedIndex, each [Sales] = targetSales),
        AddedID = Table.AddIndexColumn(SelectedTargetSales, "ID", 1, 1),
        JoinedTables = Table.NestedJoin(AddedIndex,"Index", AddedID,"Index", "WithID", JoinKind.LeftOuter),
        ExpandedWithID = Table.ExpandTableColumn(JoinedTables, "WithID", {"ID"}, {"GroupId"}),
        FilledDownID = Table.FillDown(ExpandedWithID,{"GroupId"}),
        GroupedID = Table.Group(FilledDownID, {"GroupId"},
            {{"GrpID", each Table.FirstN(_, nbRowBeneath+1),
            type table [Last Name=text, Sales=number, Product Type=text, Company=text, Index=number, ID=number]
            }}
        ),
        ExpandedData = Table.ExpandTableColumn(GroupedID, "GrpID",
            {"Last Name", "Sales", "Product Type", "Company"}
        ),
        QueryOut = if posFirstTarget = -1
            then EmptyTable
            else ExpandedData
    in
        QueryOut

    Wednesday, March 11, 2020 8:10 AM