Forum Discussion

sjorshijgenaar's avatar
sjorshijgenaar
Frequent Visitor
5 years ago

Power Query: Multiple actions in if statement

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

 

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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.

  • Jamesmry's avatar
    Jamesmry
    New Member
    4 months ago

    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.