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 ...
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.
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 ?

  • Vmcbride1's avatar
    Vmcbride1
    Regular Visitor
    6 years ago

    unfortunately no, i have tried all different variations at this point, don't know if the " " is correct looking for the blank in between the RDG and 021 as i have put a space in between the " " and took the space out as well. 

    I really appreciate the responses just been playing with this for awhile now trying to figure some way to get this to pull the data as when i wrote this in Crystal reports it was just a simple 

    if {MNBDD.BD_IN} = "$$$WIPINVENTORY" then
    if mid({MNBDD.BD_DES},5,1) = " " and mid({MNBDD.BD_DES},10,1) = " " then
    "Labor"
    else
    "Parts"
    else
    "Parts"