Forum Discussion

aditya's avatar
aditya
Copper Contributor
Oct 03, 2020
Solved

Loop Command in Power Query

Hi to all,   Stuck up with large set of data which has multiple variables and overlapping sets of values.   To overcome the same, Through "Power Query", I have managed to get non-overlapped value...
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 03, 2020

    aditya 

    As variant, to receive such result

    and ignoring column Value in source data, not sure for what it is needed, we may generate list from start to end for each record, group table on Item and Side, as aggregation union lists for the groups and count them. Entire script is

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Add column with lists of points" = Table.AddColumn(
            Source,
            "List values",
            each {[Start]+1..[End]}
        ),
        #"Groupe by Items and Sides and count union" = Table.Group(
            #"Add column with lists of points",
            {"Item", "Side"},
            {
                {"Count",
                 each List.Count(List.Union([List values]))
                }
            }
        )
    in
        #"Groupe by Items and Sides and count union"

Resources