Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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"
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"
Try this:
if [PartNum] = "$$$WIPINVENTORY" and Text.Range([DESCRIPTION],5,1) =" " and Text.Range([DESCRIPTION],10,1)=" " then "Labor1" else "Parts1"
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"
What about 4,1 and 8,1 ?
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.