Is there a way to create an "If Then" Group By in Power Query using parameter values?

[MGTexas123]

I am trying to create a Power Query with a parameter that what would perform the Table.Group function based on the parameter value. For example, if the parameter value Group by Region= "Yes", then sum up the sales and profits for each region. If the parameter value does not equal "Group by Region"="No", do not perform the group function. Here was the M code which produced the error message:


let
GroupByRegion="Yes",
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Profits", Int64.Type}, {"Sales", Int64.Type}}),
#"Conditional Parameter" = Table.AddColumn(#"Changed Type", "Custom", each if GroupByRegion="Yes" then Table.Group({"Sales Region"}, {{"Profits", each List.Sum([Profits]), type number}, {"Sales", each List.Sum([Sales]), type number}}) else 0)
in
#"Conditional Parameter"

When trying to run the query, I get this error message:

Expression.Error: 2 arguments were passed to function which expects between 3 and 5. Details: Pattern= Arguments=List


Any help on this would be greatly appreciated! Thanks!

 

[sandy666]

where is your "Sales Region"?

btw. I suggest to use [CODE]...[/CODE] tags

 

[MGTexas123]

Thanks Sandy.

Basically, I am trying to create a Power Query which will sum up certain rows of data based on a condition set by a parameter. Here is the source data:

Profits	Sales	Sales Region
1	333	North
23	4	West
4	33	East
5	5	West
5	5	North
3	325	East
2	833	East

If a parameter is set to sum by sales region, I’d like the results to look like this:

Sales Region	Profits	Sales
North	6	338
West	28	9
East	9	1191

If parameter is not set to sum by sales region, the results would look like this:

Profits	Sales	Sales Region
1	333	North
23	4	West
4	33	East
5	5	West
5	5	North
3	325	East
2	833	East

Any advice or recommendations would be greatly appreciated.

 

[Fluff]

Cross posted Is there a way to create an "If Then" Group By in Power Query using parameter values?

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[MGTexas123]

Cross posted Is there a way to create an "If Then" Group By in Power Query using parameter values?

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Thanks for letting me know. I am new to posting to forums and your suggestion to link to the other posts is much appreciated.

 

[sandy666]

IF (Conditional Column) accept only Value, Column or Parameter
You are trying to add Table

maybe it will help a bit

Creating Dynamic Parameters in Power Query

In this article I'll show how my classic method of Creating Dynamic Parameters in Power Query can be modified to display them as "Real" Parameters.

www.excelguru.ca

Power Query Parameters: 3 methods + 1 simple example

In this post we look at Power Query Parameters. These are a powerful, yet easy to make queries more dynamic and flexible for our reporting needs.

exceloffthegrid.com

 

[sandy666]

or maybe something like this:

 

[MGTexas123]

Thanks Sandy! You are the best! Very helpful information. I appreciate you taking the time to share it with me.

Best wishes,
Matt

 

[sandy666]

But I don't know you want solution from post#7 or not or you'll try to resolve the problem via post#6.