Forum Discussion

Vmcbride1's avatar
Vmcbride1
Regular Visitor
6 years ago
Solved

IF statement with 2 columns

I am very new to Power BI, so this may be a lot less difficult than I am making it out to be. I have a query with a Part# column and Description Column, what I am needing is to add a column where if the Partnum column equals a certain part number then i need it to look at the next column Description to decide if it is a part or labor entry, this is what i wrote adding a custom column but can't get it to work? Any help would be greatly appreciated

 

if [PartNum] = "$$$WIPINVENTORY" then

 if Text.Range ([DESCRIPTION],5,1) =" "and Text.Range ([DESCRIPTION],10,1)=" "then "Labor1" else "Parts1"

 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Vmcbride1 

    You could add custom columns

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc09DoMwDAXgq0SesVTyR3sWxJDWDhWjQxXl9hiGtB0s5X2R/eYZEgwgnI20j9moZCqwDDM8lVMudIWXBv1ar3AurIUMEZlCKVPX0Y4OrUNnre94Zh/QemdDR+8w+OmGU/yaVhth0anMXVMhlJ1xe+efprNID0SM0yOGe3eW2t+VdU92o8Z/uLcGy3IA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [cate = _t, des = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cate", type text}, {"des", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [cate] = "a" then 1 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.3", each if Text.Combine({Text.Middle([des], 3, 1), Text.From("*", "en-US")}, "") = " *" then 1 else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if Text.Combine({Text.Middle([des], 7, 1), Text.From("*", "en-US")}, "") = " *" then 1 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column2", "Custom.4", each if [Custom]=1 then if [Custom.3]=1 and [Custom.2]=1 then  "Labor1" else "Parts1" else " ")
in
    #"Added Custom1"

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Try this:

     

    if [PartNum] = "$$$WIPINVENTORY" and Text.Range([DESCRIPTION],5,1) =" " and Text.Range([DESCRIPTION],10,1)=" " then "Labor1" else "Parts1"
    • Vmcbride1's avatar
      Vmcbride1
      Regular Visitor
      6 years ago

      ok, i am not getting ther error now but i guess the Text.Range is a little different than Mid?

      In the 2nd column of my example the line RDG O21 1904030102 is actually a line of labor. So i am trying to pick up the blank space after the RDG and blank space after the O21 as none of my actual part numbers will have those two spaces if that makes sense. So i have tried 2,1 and 6,1 to get the blanks as well as 4,1 and 7,1 all i get to return is "Parts1"

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        What about 4,1 and 8,1 ?

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Vmcbride1 

    You could add custom columns

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc09DoMwDAXgq0SesVTyR3sWxJDWDhWjQxXl9hiGtB0s5X2R/eYZEgwgnI20j9moZCqwDDM8lVMudIWXBv1ar3AurIUMEZlCKVPX0Y4OrUNnre94Zh/QemdDR+8w+OmGU/yaVhth0anMXVMhlJ1xe+efprNID0SM0yOGe3eW2t+VdU92o8Z/uLcGy3IA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [cate = _t, des = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cate", type text}, {"des", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [cate] = "a" then 1 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.3", each if Text.Combine({Text.Middle([des], 3, 1), Text.From("*", "en-US")}, "") = " *" then 1 else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if Text.Combine({Text.Middle([des], 7, 1), Text.From("*", "en-US")}, "") = " *" then 1 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column2", "Custom.4", each if [Custom]=1 then if [Custom.3]=1 and [Custom.2]=1 then  "Labor1" else "Parts1" else " ")
in
    #"Added Custom1"

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.