Forum Discussion
Loop Command in Power Query
- 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"
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"I'm beginner level for Power Query, Can you suggest some good book / weblinks for getting more hands on Power Query Advance Commands.
- SergeiBaklanOct 05, 2020Diamond Contributor
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.