MailTo Calculated Column for Columns with Multiple Email Addresses

[RH10]

Hello everybody,

I am importing data from a SharePoint online list into Power BI. In my Power BI report, I have a table with a column that has email addresses and I would like my users to be able to click on an email address and have Outlook open up so they can send an email. However, the email address column in the SharePoint list is multi-select and some of the rows have multiple email addresses. An example of the data that I'm working with is as follows:

Course Name	Email Address
History	[email protected]
Biology	[email protected]; [email protected]
Math	[email protected]
Chemistry	[email protected]; [email protected]

From searching the web, I have found that normally you would use DAX to create a calculated column with the concatenate function as follows:

MailTo = CONCATENATE("mailto:", 'Table'[Email Address])

Then you would go to the table properties in the report and turn on Web URL under Cell elements and configure it. However, this process does not work when there are multiple email addresses in a row. The goal is to have each individual email address in each row open Outlook when clicked. For example, for the Biology row, clicking on [email protected] would open Outlook with [email protected] as the recepient, and clicking on [email protected] in the same row, would also open Outlook with [email protected] as the recepient. Is there a way to go about doing this?

Thank you for your help!

[lbendlin]

You need the opposite of concatenate - you need to split your [Email Address] string  at the "; "  boundary and fetch the first item from the list. 

You can do that in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgsLskvqlTSUcqAsByKkzPy83P08ovSlWJ1opWcMvNz8tNBCpIgLCQF1grFyZmpecmp6Jp8E0sygDpygRS6lHNGai7QJrCVyTA2QTNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Course Name" = _t, #"Email Address" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Course Name", type text}, {"Email Address", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Email Address", Splitter.SplitTextByEachDelimiter({"; "}, QuoteStyle.Csv, false), {"Email Address.1", "Email Address.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Email Address.1", type text}, {"Email Address.2", type text}})
in
    #"Changed Type1"

or you can do it in DAX, using a replacer and the PATHITEM function.

Link = "mailto:" & PATHITEM(SUBSTITUTE([Email Address],"; ","|"),1,TEXT)

[RH10]

Hello lbendlin,

Thank you for your response. However, this solution ignores other email addresses in each row. My organization requires me to have each email address in each row be clickable and open the respective email address when clicked on. For example, in the data I gave above, if I were to turn that into a table visualization in Power BI, for the Biology row, clicking on [email protected] would open Outlook with that [email protected] as the recepient, and clicking on [email protected], would also open Outlook with [email protected] as the recepient. Is this possible? I apologize if I didn't make it clear before, I have edited the topic post to include that detail. Thank you again for your help.

[lbendlin · Accepted solution]

You cannot have multiple independent values in a cell. To work around that limitation would require  you to either create additional columns for each non-primary email address, or to unpivot your data, or to use HTML visuals. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgsLskvqlTSUcqAsByKkzPy83P08ovSlWJ1opWcMvNz8tNBCpIgLCQF1grFyZmpecmp6Jp8E0sygDpygRS6lHNGai7QJrCVyTA2QTNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Course Name" = _t, #"Email Address" = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Email Address", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Email Address")
in
    #"Split Column by Delimiter"

[RH10]

I decided to add additional columns, thank you for this solution!