Power Query - Replace Formula Operator with Parameter

[Anonymous]

Hi,

I want to replace the operator in one of my power query formulas with a parameter, so I can adjust the formula without having to go into Power Query to find the step and update it manually.

Below is an example of a simple formula:

= Table.AddColumn(#"Campaign: Taxonomy Match Indicator", "Custom", each if [total_impressions] = 100 then "YES" else "NO")

I've created a text parameter with "=" and ">=" as the list options, as below:

"=" meta [IsParameterQuery=true, List={"=", ">="}, DefaultValue="=", Type="Text", IsParameterQueryRequired=true]

I want to add this parameter to the formula step, replacing "=" in the formula with the parameter. So I can either look for an exact number of impressions or a minimum number. But I'm not sure how to add this into the M Language so it can be dynamic.

Does anyone have any ideas on how I can do this?

Thanks,

Mark

[Anonymous]

Hi @wdx223_Daniel ,

Thanks for this. I've tried it on this simple version and it works. But now I'd like to try it with something a little more complex. Here's the original query step I want to use:

= Table.AddColumn(#"Campaign: Taxonomy Pattern", "Taxonomy Match - DV360 Campaign Name", each if List.Count(#"01a: DV360 Campaign Names Fields") = 0 then "No Taxonomy Pattern" else if List.Count( Text.ToList(Text.Select([campaign_name], #"01a: DV360 Campaign Name Delimiter"))) = List.Count(#"01a: DV360 Campaign Names Fields")-1 then "Correct Pattern" else "Pattern Error", Text.Type)

This step looks at a field called Campaign_Name. It looks to identify the number of delimiters in the field. The delimiter comes from a parameter, called #"01a: DV360 Campaign Name Delimiter". It's then comparing this to a list of the delimiter fields. So if the Campaign_Name has the required number of delimiters, it will pass. 

I want to be able to add my new parameter in place of the "=" so I can either have an exact or minimum number of delimiters matching. 

I've taken your pattern and tried to use it on this, as follows:

= Table.AddColumn(#"Campaign: Taxonomy Pattern", "Taxonomy Match - DV360 Campaign Name", each if List.Count(#"01a: DV360 Campaign Names Fields") = 0 then "No Taxonomy Pattern" else if Expression.Evaluate("List.Count( Text.ToList(Text.Select([campaign_name], #"01a: DV360 Campaign Name Delimiter")))"&#"01a: DV360 Campaign Name Exact or Minimum"&"List.Count(#"01a: DV360 Campaign Names Fields")-1",[_=_]) then "Correct Pattern" else "Pattern Error", Text.Type)

But when I execute this, I get the following error: Expression.SyntaxError: Token ',' expected.

Any idea what I need to amend to correct this?

Thanks,

Mark

[wdx223_Daniel]

i did not get your point, and why your original code cannot get what you want? 

[Anonymous]

Hey,

I wanted to start with a simple example to see if I could take that and apply it to my more complex example. The simple version works but for some reason the more complex example doesn't. The error I'm getting says it's expecting ',' somewhere.

I'll breakdown the formula:

COUNT OF DELIMITERS: List.Count( Text.ToList(Text.Select([campaign_name], #"01a: DV360 Campaign Name Delimiter")))

REPLACE WITH PARAMETER: =

TARGET DELIMITERS: List.Count(#"01a: DV360 Campaign Names Fields")-1

Does that help?

[wdx223_Daniel]

Text.From(List.Count( Text.ToList(Text.Select([campaign_name], #"01a: DV360 Campaign Name Delimiter"))))&ParameterValue&Text.From(List.Count(#"01a: DV360 Campaign Names Fields")-1)

is this ok?

[Anonymous]

Hey,

It throws up errors with the following:

[wdx223_Daniel]

put those into Expression.Evaluate

[Anonymous · Accepted solution]

Success, it works!

Here's the final code:

Expression.Evaluate(Text.From(List.Count( Text.ToList(Text.Select([line_item], #"01c: DV360 Line Item Delimiter - Display"))))&#"01a: DV360 LI Exact or Minimum"&Text.From(List.Count(#"01c: DV360 Line Item Fields - Display")-1),[_=_])

Thanks so much for your help!

[wdx223_Daniel]

= Table.AddColumn(#"Campaign: Taxonomy Match Indicator", "Custom", each if Expression.Evaluate("[total_impressions]"&ParameterValue&"100",[_=_]) then "YES" else "NO")