Forum Discussion
Loop Command in Power Query
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"
aditya Not sure if I fully comprehended your problem, but perhaps the attached workbook contains the result you are after. I've added a query "Table1 (2)" that loads a four row table.
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 | |||||||||
| Item | Side | Start | End | Value | Item | Side | Start | End | LENGTH | |
| Item1 | A | 100 | 300 | 1 | Item1 | A | 100 | 400 | 300 | |
| Item1 | A | 200 | 250 | 1 | Item1 | A | 500 | 600 | 100 | |
| Item1 | A | 100 | 150 | 1 | Item1 | A | 750 | 850 | 100 | |
| Item1 | A | 500 | 600 | 1 | Item1 | A | 900 | 1000 | 100 | |
| Item1 | A | 280 | 400 | 1 | ||||||
| Item1 | A | 750 | 800 | 1 | Item1 | A | 600 | |||
| Item1 | A | 800 | 850 | 1 | ||||||
| Item1 | A | 900 | 1000 | 1 |
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"- Thanks, Works exactly with what I was looking for..
I'm beginner level for Power Query, Can you suggest some good book / weblinks for getting more hands on Power Query Advance Commands.You are welcome, glad to help. As for resources - lot of them. You may start from Gil Raviv book https://datachant.com/next/ and his blog, lot of topics on Ken Puls' excelguru.ca , next could be Chris Webb' BI blog.
aditya Perhaps like in the attached file. Split off the original query and loaded two tables. One before aggregation and one after.
Riny_van_EekelenThanks for the efforts mate.. But it misses ranges between multiple data set as under.
Item1 A 100 300 Item1 A 200 250 Item1 A 100 150 Item1 A 500 600 Item1 A 280 400 Item1 A 750 800 Item1 A 800 850 Item1 A 900 1000 Should provide resultant as
Merged Start End Subtraction Item1|A 100 400 300 Item1|A 500 600 100 Item1|A 750 850 100 Item1|A 900 1000 100 Hence, Total of Item1|A shall be 600.
Solution provided by @Sergei Baklan covers the same.
Thanks Amigos.