Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
th3h0bb5
Resolver II
Resolver II

Powerquery- Multiple actions in IF statement

I have created at variable equal to 1. I want to iterate through a column and (if the value is null) do two things:

  • Replace the null with my variable
  • Update my variable, increasing it by 1

Something like so:

 

each if [Column] = null then variable AND variable=variable+1 else [Column]

 

What's the proper syntax to replace AND with?

1 ACCEPTED SOLUTION

Hi @th3h0bb5 ,

 

Check if it works:

Capture.PNG

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyMTUD03mlOTmoDHMLS6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Index" = Table.Combine(
{Table.SelectRows(#"Changed Type", each [Column1] <> null),
Table.AddIndexColumn(Table.SelectRows(#"Changed Type", each [Column1] = null), "Index", 1, 1)
}),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Column1", "Column1 - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Column1 - Copy", type text}, {"Index", type text}}, "pt-BR"),{"Column1 - Copy", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column2")
in
#"Merged Columns"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

9 REPLIES 9
edhans
Super User
Super User

Power Query uses lower case and.

 

Other than that, I don't understand your formula. PQ uses if/then/else, and you can nest if statements.

 

each if [Column] = null then variable AND variable=variable+1 else [Column]

 

if the column null, you are saying "then variable and variable = variable +1" = that is a boolean logic comparison, which I cannot make sense of. Can you provide an actual example?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

'AND' in my example above is a placeholder, as I don't actually now the proper thing to put there. I'm not exactly sure how else to restate this. You can look at the sample input/output columns I provided above if that helps.


In English, I want a line of code that says:

  1. Store a variable equal to 1.
  2. Go through each row of a column.
  3. If the current value is null, then replace that null with my variable (which is currently 1). Also add 1 to my stored variable (making it 2).
  4. Otherwise, leave that current value alone

I want Powerquery to perform 2 actions if my criteria is true. In code-lish it would "IF true THEN do 1 and 2 ELSE do 3."

 

In most programming languages, this could be easily done with by storing a variable and then iterating through a list with a FOR LOOP. 

Greg_Deckler
Super User
Super User

Not sure if you want:

image.png

 

or "if then else if then else"  ? I don't know what your logic is from what you posted.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

What I'd like is x2 actions to happen during the 'THEN' portion of the IF statement:

 

IF [column] = null

     THEN DoThing1 and DoThing2

     ELSE DoThing3

I'm probably going to have to bail on this and call in @ImkeF and @edhans . Maybe it's just because I can't see the rest of the code or how this is used in context of a column or a function or query but seems to me that you would need to use another let statement.

 

if blah=blah then let var = blah, var2 = blah in ...

 

https://bengribaudo.com/blog/2018/01/19/4321/power-query-m-primer-part4-variables-identifiers


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Exactly what I was looking for! Very helpful for retrieving data only if a certain parameter condition is fulfilled.

I'll check out the article!

 

My goal is to create an auto-incrementing Index but only for rows where my Key is Null (it's a long, messy story). If Column1 below is what I had, my desired output is Column2.

 

Column1Column2
123123
456456
 1
 2
789789

 

To do this, I am wanting create a Conditional column like so:

Table.AddColumn(
     Source,

     "Column2",

     each

          if [Column1] = null

          then indexVariable and indexVariable=indexVariable+1

          else [Column2])

 

Hi @th3h0bb5 ,

 

Check if it works:

Capture.PNG

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyMTUD03mlOTmoDHMLS6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Index" = Table.Combine(
{Table.SelectRows(#"Changed Type", each [Column1] <> null),
Table.AddIndexColumn(Table.SelectRows(#"Changed Type", each [Column1] = null), "Index", 1, 1)
}),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Column1", "Column1 - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Column1 - Copy", type text}, {"Index", type text}}, "pt-BR"),{"Column1 - Copy", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column2")
in
#"Merged Columns"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 Thanks! I ended up doing something very similar to your solution. I created an index column and just used that to replace my null values instead of a an incrementing variable.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.