March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I have a table myTable with 21 columns :
The first one is the column "key"
The others are named "value_1", "value_2" .... "value_20"
I have a list that contains a subset of the column names of myTable : myList = {"value_6", "value_12", "value_18"}
This list is not frozen. It can change, with 1, 2, 3, 4, 5... items.
How can I apply a group by calculation on myTable for the subset of columns names present in myList ?
It should be something like :
myStep = Table.Group(
myTable,
{"key"},
... here create on the fly a list of aggregation descriptions based on myList ... (aggregation function be a sum) ...
)
And I'm interesting in having a deep understanding on how M(PQ) iterate over the aggregation description list in the Table.Group function.
thanks for
Solved! Go to Solution.
Or with Table.Group
= Table.Group(
myTable,
{"key"},
List.Transform(
myList,
each {
"Sum_"& _,
Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
type number
}
)
)
Stéphane
Or with Table.Group
= Table.Group(
myTable,
{"key"},
List.Transform(
myList,
each {
"Sum_"& _,
Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
type number
}
)
)
Stéphane
Hi, @jct999
Select Columns, Unpivot then Pivot
let
Source = myTable,
SelectColumns = Table.SelectColumns(Source,{"key"} & myList),
UnPivot = Table.UnpivotOtherColumns(SelectColumns, {"key"}, "Attribute", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[Attribute]), "Attribute", "Value", List.Sum)
in
Pivot
Stéphane
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.