cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Boja Member
Member

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

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

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

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
Highlighted
MarcelBeug Super Contributor
Super Contributor

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

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)
Boja Member
Member

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

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 Super Contributor
Super Contributor

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

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

Boja Member
Member

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

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 391 members 3,406 guests
Please welcome our newest community members: