Group By - dynamic column selection with Parameters

Power Query
1

I’m looking for a method to dynamically select the columns on which a table with data is to be grouped, summarizing sales and items sold depending on the grouping.

I’ve got a number of sources that deliver very granular data, in some cases I need to zoom into this granularity, in others it’s not really needed.
In order to optimize the queries I’m looking for ways to minimize the number of rows by adjusting only a few parameters instead of having to dive into the table.group formulas for each source.

eDNA Forum question - Group By - dynamic column selection
eDNA Forum question - Group By - dynamic column selection1106×568 37.9 KB

Appending all the queries before the grouping step is one option I’ve considered, but I’m specifically looking to do the grouping and minimizing the rows in an earlier stage.

My guess is combining Table.Group with an if statement and parameters (true/false) for the columns that I’d like to exclude or include depending on the situation, but I’m not sure where to start with the if statement.

Below is an example .pbix. In the query editor you will find an example setup and parameters set to define which columns to include in the Group By. Any help would be much appreciated!

eDNA Forum question - Group By - dynamic column selection.pbix (83.9 KB)

Any other suggestions for a similar setup are welcome also!

1 Like
2

@Maikelshuvit Try this:
eDNA Forum question - Group By - dynamic column selection.pbix (84.1 KB)

2 Likes
3

@AntrikshSharma Even though I still need to completely figure out the logic, this works great. Thanks a lot!

4

@Maikelshuvit I have created a List of List that contains pair of the Query Name and the Boolean value returned, if the second value in each list is True then return the Query name else Null and finally remove the Null as well.

if _{1} = true then _{0} else null

1 = True/False, 0 = Query Name

List.Transform allows you to modify a list in any way you can think, it can either retain the original value or replace it with complete different value.

Try these:

= List.Transform ( { 1…5 }, each "Hello " & Text.From ( _ ) )

= List.Transform ( { 1…5 }, each “Numbers are now lost” )

= List.Transform ( { 1…5 }, each { _, “This code now returns a List of List”} )

In the Table.Group the second parameter accepts values as a list you can supply a list create in any way as long as the column names match

2 Likes
5

@AntrikshSharma Incredible… thanks so much. Power Query keeps on surprising me

2 Likes