Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jct999
Advocate II
Advocate II

Dynamic grouping with M (PQ)

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 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Or with Table.Group

= Table.Group(
myTable,
{"key"},
List.Transform(
myList,
each {
"Sum_"& _,
Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
type number
}
)
)

Stéphane

View solution in original post

2 REPLIES 2
slorin
Super User
Super User

Or with Table.Group

= Table.Group(
myTable,
{"key"},
List.Transform(
myList,
each {
"Sum_"& _,
Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
type number
}
)
)

Stéphane

slorin
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.