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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors