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 for single item in sheet 'Length Achieved - Single Item'. (Item1 - A is having value of 400)

 

However, I'm unable to apply the same with Multiple Item in sheet 'Length Achieved - Multiple Item'.. ((Item1 - A value changes to 950)

 

I need help in Running Same Sets of Command in Loop. Sheet Attached.

 

Thanks in Advance..

BI & Data Analysis
developer
excel
power bi
User Adoption
  • 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"

7 Replies

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

      Riny_van_Eekelen  Attached solves one part of problem of looping.

       

      However, It is not removing and capturing overlapping & non-overlapping data. Sample data for single item is shown below. And Power Query of Single Item is attached in previous post named as "Length Achieved -Single Item".

       

      Data with same set of (ITEM & SIDE) Resultant
      ItemSideStartEndValue ItemSideStartEndLENGTH
      Item1A1003001 Item1A100400300
      Item1A2002501 Item1A500600100
      Item1A1001501 Item1A750850100
      Item1A5006001 Item1A9001000100
      Item1A2804001      
      Item1A7508001 Item1A  600
      Item1A8008501      
      Item1A90010001      

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        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