Power Query: Multiple actions in if statement

[sjorshijgenaar]

Hey all,

So I'm basically trying to get the answer @th3h0bb5 never got: https://community.powerbi.com/t5/Desktop/Powerquery-Multiple-actions-in-IF-statement/td-p/1086609. Or @jthomson for that matter: https://community.powerbi.com/t5/Desktop/If-statement-multiple-actions/m-p/360729#M162818. 

I want to have an if statement where the then and else contain two (or more) actions, so following the following pattern:

if a = b
then
  do action 1
  do action 2
else
  do action 3
  do action 4

I read somewhere that should be possible by using let ... in however Power Query treats it like a function rather than performing the actions, asking me for a parameter ( _ (optional) )

= each if Text.StartsWith([Factuurnummer], "VND")
  then 
    let
      #"Invoked Custom Function1" = Table.AddColumn(#"Added Conditional Column", "11", each "1"),
      #"Expanded Abonnementskosten" = Table.AddColumn(#"Invoked Custom Function1", "12", each "2")
    in 
      #"Expanded Abonnementskosten"
  else
    let
      Test = Table.AddColumn(#"Added Conditional Column", "Abonnementskosten", each "test")
    in 
      Test

[Jamesmry]

Hi there,

Instead of creating a new post quoting this one and th3h0bb5's request, I am also interested in the answer to that question: @ImkeF would you mind sharing that solution here on in PM?

In my case, I try to perform two actions following a condition checking the text-value of a cell in a fixed column (which will reiterate for every table line):

if [ColumnA] = "DirectionA" then "DGDU" and [ColumnB] = "SpecificValue"

I cannot use the let...in function (as recommended by AIs) it is already implemented in my PowerQuery table.

Therefore I have tried multiple variants to make it work by adding a custom column in which I write the following:

else if [ColumnA] = "DirectionA" then [ColumnA = "DGDU", ColumnB = "SpecificValue"]

which gives me the following output in the newly created ColumnA :

Test-1

Problem:

• As you can see, no error, but it does not give the expected value;
• The cell in columnB is unchanged.

Tried this:

else if [ColumnA] = "DirectionA" then "DGDU" and [ColumnB] = "SpecificValue"

Test-2

Problem:

• I get an error here and the cell in columnB remains unchanged.

And this:

Table.ReplaceValue(TABLE, each [ColumnA], each if [ColumnA]="DGDU" then [ColumnB]="SpecificValue" else [ColumnA],Replacer.ReplaceValue,{"ColumnA"})

Test-3Test-3bis

Problem:

• I could not find a way to add this code-line after my other "Ifs" in the added column so I had to create a new one;
• Still get an error;
• I get the PowerQuery following error message when I want to move the column to another place (understand "Expression.Error: A cyclic reference was encountered during evaluation.").

And this, suggested by IA but cannot find a way to implement it correctly in the advanced PoweryQuery editor without changing the rest that it is already written (and is also not really what I am looking for):

else if Text.Contains([ColumnA], "DirectionA") then
		let
			xColumnA = Text.Replace([ColumnA], "DirectionA", "DGDU"),
			xColumnB = Text.Replace([ColumnB], "", "SpecificValue"),
			// Return a record with both changes
			Result = [ColumnA = xColumnA, ColumnB = xColumnB]
		in
			Result
	else
		[xColumnA = [ColumnA], xColumnB = [ColumnB]]

And eventually this, which still does not work but at least it keeps it simple:

else if [ColumnA] = "DirectionA" then "DGDU"
else if [ColumnA] = "DGDU" then [ColumnB] = "MPC"

Test-4

Problem:

• The renaming works (remembering that this PowerQuery script has been written in a new custom column, renamed "NewColumnA");
• No value is written in ColumnB.

[MFelix]

Hi @sjorshijgenaar ,

I'm calling out to one of the best super users on M language that can help you out.

@ImkeF can you help gving a path to have this working.