Forum Discussion

katie_bell's avatar
katie_bell
Frequent Visitor
5 years ago

else if then loop for each value in table/list

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 be...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago
#"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's avatar
katie_bell
Frequent Visitor
5 years ago

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.