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	history@school.org
Biology	biology@school.org; science@school.org
Math	math@school.org
Chemistry	chemistry@school.org; science@school.org

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 biology@school.org would open Outlook with biology@school.org as the recepient, and clicking on science@school.org in the same row, would also open Outlook with science@school.org 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 biology@school.org would open Outlook with that biology@school.org as the recepient, and clicking on science@school.org, would also open Outlook with science@school.org 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!