Forum Discussion
MailTo Calculated Column for Columns with Multiple Email Addresses
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"
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)
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.
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"I decided to add additional columns, thank you for this solution!