else if then loop for each value in table/list

[katie_bell]

Hi all,

First of all, apologies for asking for what is probably a very simple solution - I'm new to Power BI and learning on the go.

I have a working M Query as shown in the shortened example below - it searches for multiple text strings per 'sender' and returns the 'sender name'.

(sidenote - my actual query consists of 12 lines for various text strings per sender, and I have approx. 80 senders).

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sender_Name",

each if Text.Contains([TextBody],"SenderNameA#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA.#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA #(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA.#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA #(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"

else if Text.Contains([TextBody],"SenderNameB#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameB"
else if Text.Contains([TextBody],"SenderNameB.#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameB"
else if Text.Contains([TextBody],"SenderNameB #(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameB"
else if Text.Contains([TextBody],"SenderNameB#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameB"
else if Text.Contains([TextBody],"SenderNameB.#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameB"
else if Text.Contains([TextBody],"SenderNameB #(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameB"

else "Sender Unknown"

The problem I have is that since the list of names is growing significantly PBI now says that the query is too complex.

To get around this issue, and to also make updating the list of sender names more managable, I would like to use a table named "Senders" such as below:

SenderNames

SenderNameA

SenderNameB

SenderNameC

and to then loop through the following for each SenderName:

else if Text.Contains([TextBody],"SenderName#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderName"
else if Text.Contains([TextBody],"SenderName.#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderName"
else if Text.Contains([TextBody],"SenderName #(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderName"
else if Text.Contains([TextBody],"SenderName#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderName"
else if Text.Contains([TextBody],"SenderName.#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderName"
else if Text.Contains([TextBody],"SenderName #(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderName"

Any help to achieve this seemingly simple task would be very much appreciated!

Cheers,
Katie

[Anonymous]

but is the string "signature text" just like that or is it a variable text and, if so, how is it related to the sendername?

[Anonymous]

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sender_Name",

each if Text.Contains([TextBody],"SenderNameA#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA.#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA #(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA.#(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"
else if Text.Contains([TextBody],"SenderNameA #(cr,lf)#(cr,lf)Signature Text", Comparer.OrdinalIgnoreCase) then "SenderNameA"

maybe I'm missing something, but it seems to me that all the if ... then else ... can be grouped into one and similarly for the others. 

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sender_Name",

each if Text.Contains([TextBody],"SenderNameA", Comparer.OrdinalIgnoreCase) then "SenderNameA"

If not, try to illustrate your real situation in more detail by providing examples of the starting table.

[katie_bell]

Hi Rocco,

The starting table is very large, and the cell that contains Textbody cell is quite long. Imagine the body of an email thread... so showing an example isn't really feasible.

The reason for the multiple if then lines is because I'm specifically searching for the signature block, which tend have various combinations, such as multiple carriage returns, full stops and spaces.

Unfortunately I cannot search for just a name, as another person within the email thread may have that same name... for example, the original senders name may be "John" and the person responding (with the signature block that I'm searching the text for) may also have the name "John". 

Basically, I need to keep the multiple if then statements, but as mentioned in my original post, what I'm after help with is just using a souce table to list the names to reference in the if then statements, and repeat the 12 or so if then statements for each name in the list.

[Vera_33]

Hi @katie_bell 

Based on your sample, you have cases of space, dot, #(cr,lf), you can use the UI to Trim, Clean, and Replace " " and "." for both [TextBody] and your lookup Signature

Then you need a look up table like this

You can add a custom column to look up sender, note: Name is my look up table

Table.AddColumn(#"Replaced Value1", "Custom", each [a = [TextBody],
b=
Table.AddColumn( Name,"TEST", each Text.Contains(a, [Signature])),
c= Table.SelectRows(b,each [TEST]= true)[SenderNames
]{0}][c])

If you can provide some sample data, I can give you a query you can copy paste to Advanced Editor and see what's happening.

[katie_bell]

Thanks so much for your assistance @Vera_33!

Is there a way I can limit the table to only sender names, and handle the varying signature blocks in my query? I'm hoping the business area that use the report can update it via an Excel spreadsheet that I will use as the source. Having them manage the signature strings will be too prone to error, so I'll need to keep it simple and only as a list of names.

Additionally, removing spaces from the text is not ideal as the data is quite long (the message body) and will also be used to search for other keywords that contain spaces and other characters.

[Vera_33]

sorry @katie_bell I have issue with my laptop. Yes, you can search sender first then match signature (part of it, maybe) as there might be people with same names...I am wondering what is your source, all text in email body only?

[Anonymous]

take a look at this setting and explain where and how you would like to change it.

i have problems (and now i don't have time to experiment with text editors) to simulate the # (lf, cr).