IF statement with 2 columns

[Vmcbride1]

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"

[v-juanli-msft · Accepted solution]

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.

[Anonymous]

Try this:

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

[Vmcbride1]

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]

What about 4,1 and 8,1 ?

[Vmcbride1]

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"