Forum Discussion
aditya
Oct 02, 2020Copper Contributor
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...
- Oct 03, 2020
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"
Riny_van_Eekelen
Oct 03, 2020Platinum Contributor
aditya Perhaps like in the attached file. Split off the original query and loaded two tables. One before aggregation and one after.
aditya
Oct 05, 2020Copper Contributor
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.