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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Boja
Advocate II
Advocate II

If Text.Contains element from the list then write... but ERROR

Hello,

 

I am struggling with this problem for a while and i would trully appreciate any help. What i need is to make a column according to following conditions:

 

if column "line of business" is blank or "Multi LOB" or "ITO" then search values as follows>

 

If column "Class path" contains "Security"  then write " Security Solutions"

If column "Class path" contains "Collaboration"  then write " Customer Solutions"

If column "Class path" contains "Network"  then write " Network Solutions"

 

If there is nonmatch or column "Class path' is empty, then 

if If column "Tech path" contains "Security"  then write " Security Solutions"

If column "Class path" contains "Collaboration"  then write " Customer Solutions"

If column "Class path" contains "Network"  then write " Network Solutions"

 

else write original value from a column "line of business"

 

my code is partly working. the problem is that it returns error if any of these 2 columns has null value. Maybe SWITCH is more appoprite here, but i didn't know how to implement it on multiple conditions.

 

Any suggestions, please?

1.png

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

Power Query has no CHOOSE function. You created your own function. Anyhow it IS Power Query, so that's good. 😉

 

3 issues:

  1. You can't refer to field [line of business] inside the function.
    Instead, you can add another argument to the function.
  2. The lookup won't work with null values, so they should be replaced by "".
  3. The code is overly complicated.

My suggestion would be to add a column that combines [tech path] and [class path], replacing nulls with "".

Then you can add a column without using a function.

 

let
    Source = #table(type table[line of business = text, tech path = text, class path = text],{{null,"MarcelBeug",null}}),
    #"Added Custom1" = Table.AddColumn(Source, "class tech path", each Replacer.ReplaceValue([class path],null,"") & Replacer.ReplaceValue([tech path],null,"")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each if [line of business]=null or [line of business]="Microsoft Solutions" or [line of business]="Multi LOB" or [line of business]="ITO"
then

if Text.Contains([class tech path], "Security") then "Security Solutions" 
else if Text.Contains([class tech path], "Collaboration") then "Converged Communications" 
else if Text.Contains([class tech path], "Customer") then "Customer Interactive Solutions" 
else if Text.Contains([class tech path], "Data C") then "Data Centre Solutions"
else if Text.Contains([class tech path], "Networking") then "Network Integration" 
else [line of business] else [line of business])
in
    #"Added Custom2"

 

Alternative:

If you still prefer a lookup function, then my suggestion would be to use a record, instead of a lookup list.

As your lookup values are text, you can just refer to the values by using the field name.

With Record.FieldOrDefault you can also provide a default value for non-existing fields.

In the query below, field [line of business] is passed as second argument (default).

The function also takes care of replacing null values.

 

Function code:

 

let
    fnChoose_LOBCodeMB = (input, default) => 
    let
        values = 
            [SS  = "Security Solutions",
             CIS = "Customer Interactive Solutions",
             NI  = "Network Integration",
             CC  = "Converged Communications",
             DCS = "Data Centre Solutions"],
        InputNonNull = if input = null then "" else input,
        Result = Record.FieldOrDefault(values,InputNonNull,default)
    in
        Result
in
    fnChoose_LOBCodeMB

 

Query code (close to your original query):

 

let
    Source = #table(type table[line of business = text, tech path = text, class path = text],{{null,"Boterberg",null}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [line of business]=null or [line of business]="Microsoft Solutions" or [line of business]="Multi LOB" or [line of business]="ITO"
then

fnChoose_LOBCodeMB (

try if Text.Contains([class path], "Security") or Text.Contains([tech path], "Security") then "SS" 
else if Text.Contains([class path], "Collaboration") or Text.Contains([tech path], "Collaboration")  then "CC" 
else if Text.Contains([class path], "Customer") or Text.Contains([tech path], "Customer") then "CIS" 
else if Text.Contains([class path], "Data C") or Text.Contains([tech path], "Data C") then "DCS" 
else if Text.Contains([class path], "Networking") or Text.Contains([tech path], "Networking") then "NI" 
else "X" otherwise null
,[line of business])

else [line of business])
in
    #"Added Custom"
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

Rather confusing if you are looking for a Power Query (Text.Contains) or a DAX (SWITCH) solution.

 

Why not share (the essential part of) your code that is not working as desired?

Then we can proceed from there.

Specializing in Power Query Formula Language (M)

Hi Marcel,

 

thank you for reply. Here is the code i tried. Sorry for confusion, i mean CHOOSE instead SWITCH.

 

 Create f-on for LOB ==>
fnChoose_LOBCode = (input) => let
values = {
{"SS", "Security Solutions"},
{"CIS", "Customer Interactive Solutions"},
{"NI", "Network Integration"},
{"CC", "Converged Communications"},
{"DCS", "Data Centre Solutions"},
{"X", [line of business]},

{input, [line of business]}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,


Create new clm===>

=if [line of business]=null or [line of business]="Microsoft Solutions" or [line of business]="Multi LOB" or [line of business]="ITO"
then

fnChoose_LOBCode (

try if Text.Contains([class path], "Security") or Text.Contains([tech path], "Security") then "SS" 
else if Text.Contains([class path], "Collaboration") or Text.Contains([tech path], "Collaboration")  then "CC" 
else if Text.Contains([class path], "Customer") or Text.Contains([tech path], "Customer") then "CIS" 
else if Text.Contains([class path], "Data C") or Text.Contains([tech path], "Data C") then "DCS" 
else if Text.Contains([class path], "Networking") or Text.Contains([tech path], "Networking") then "NI" 
else "X" otherwise null
)

else [line of business]

 the main problem with this code is that it returns error whenever any of lookup columns has a null value.

 

MarcelBeug
Community Champion
Community Champion

Power Query has no CHOOSE function. You created your own function. Anyhow it IS Power Query, so that's good. 😉

 

3 issues:

  1. You can't refer to field [line of business] inside the function.
    Instead, you can add another argument to the function.
  2. The lookup won't work with null values, so they should be replaced by "".
  3. The code is overly complicated.

My suggestion would be to add a column that combines [tech path] and [class path], replacing nulls with "".

Then you can add a column without using a function.

 

let
    Source = #table(type table[line of business = text, tech path = text, class path = text],{{null,"MarcelBeug",null}}),
    #"Added Custom1" = Table.AddColumn(Source, "class tech path", each Replacer.ReplaceValue([class path],null,"") & Replacer.ReplaceValue([tech path],null,"")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each if [line of business]=null or [line of business]="Microsoft Solutions" or [line of business]="Multi LOB" or [line of business]="ITO"
then

if Text.Contains([class tech path], "Security") then "Security Solutions" 
else if Text.Contains([class tech path], "Collaboration") then "Converged Communications" 
else if Text.Contains([class tech path], "Customer") then "Customer Interactive Solutions" 
else if Text.Contains([class tech path], "Data C") then "Data Centre Solutions"
else if Text.Contains([class tech path], "Networking") then "Network Integration" 
else [line of business] else [line of business])
in
    #"Added Custom2"

 

Alternative:

If you still prefer a lookup function, then my suggestion would be to use a record, instead of a lookup list.

As your lookup values are text, you can just refer to the values by using the field name.

With Record.FieldOrDefault you can also provide a default value for non-existing fields.

In the query below, field [line of business] is passed as second argument (default).

The function also takes care of replacing null values.

 

Function code:

 

let
    fnChoose_LOBCodeMB = (input, default) => 
    let
        values = 
            [SS  = "Security Solutions",
             CIS = "Customer Interactive Solutions",
             NI  = "Network Integration",
             CC  = "Converged Communications",
             DCS = "Data Centre Solutions"],
        InputNonNull = if input = null then "" else input,
        Result = Record.FieldOrDefault(values,InputNonNull,default)
    in
        Result
in
    fnChoose_LOBCodeMB

 

Query code (close to your original query):

 

let
    Source = #table(type table[line of business = text, tech path = text, class path = text],{{null,"Boterberg",null}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [line of business]=null or [line of business]="Microsoft Solutions" or [line of business]="Multi LOB" or [line of business]="ITO"
then

fnChoose_LOBCodeMB (

try if Text.Contains([class path], "Security") or Text.Contains([tech path], "Security") then "SS" 
else if Text.Contains([class path], "Collaboration") or Text.Contains([tech path], "Collaboration")  then "CC" 
else if Text.Contains([class path], "Customer") or Text.Contains([tech path], "Customer") then "CIS" 
else if Text.Contains([class path], "Data C") or Text.Contains([tech path], "Data C") then "DCS" 
else if Text.Contains([class path], "Networking") or Text.Contains([tech path], "Networking") then "NI" 
else "X" otherwise null
,[line of business])

else [line of business])
in
    #"Added Custom"
Specializing in Power Query Formula Language (M)

This is great. it works!

I see that i have a lot of to learn still. I was struggling with this in days, therefore i am very grateful for your help, Marcel.

 

thank you very much Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.