Replace text within value based on another column

[NiugeS]

Hi all,

Trying to get my head around this one and if anyone has some suggestions on where I can look into it more, it would be appreciated.  I'm fairly new to PowerBI and Dax.   Table 2 is comments that have the ID of the User in table 1.  I am looking for a way for a search of [~accountid:xxxxxx] and replace it with the value in Column Name of table 1.  Desired result displayed below.

Table 1: Users
User	Name
User1	John
User2	Joe
User3	Andrew
User4	Mike
User5	Sarah
User16	Deb
User17	Sam
User18	Lucy

ID	Date	Comment	Desired Result
1234	20/03/2020	Thank you [~accountid:User1], I'll let [~accountid:User2] do the work.	Thank you John, I'll let Joe do the work.
1234	22/03/2020	No problem.  [~accountid: User4] Can you help?	No problem.  Mike Can you help?
1234	24/03/2020	Hey all - [~accountid:User4] Mike I can help	Hey all - Mike I can help
1234	24/04/2020	[~accountid:User1], [~accountid:User2], [~accountid:User3], [~accountid:User4] - all sorted!	John, Joe, Andrew, Mike - all sorted
1456	19/03/2020	Hey [~accountid: User1]	Hey John
1435	20/02/2020	Hey [~accountid:User18] - Need help with this?	Hey Lucy - Need help with this?

Looked through various posts and can't seem to find what i'm looking for.  Any suggestions welcome.

[Anonymous]

If you don't mind creating another column you can create a calculated column and use dax.

NewDivision = IF([organisation / portfolio] = "ZYX", "other", [Division])

If you have multiple organisations / portfolios values you want to do this for you can use OR or the logical or operator symbol

[camargos88]

Hi @NiugeS ,

Try this code on Advanced Editor (I assumed you already have the User tables loaded):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVK7boMwFP2VW2aSGEP6WqIqGUrUZqjaiXqgcCUQxo6MUcTSb68NVDKQTMj3npcPThIvPni+d0g1ms9e1jUKbQfYlApz+MCm5dpjfuIFNIzMhpINCTeUUGIOn0UqKuhkC8lvmmWyFbrMn78aVAHzIf5uCaEPnANHvUBQBrkEXSBcpKrWE7WjLMScf5Q4JbihqBPqJOGs5A/Heg0TV7C2EYN9KnqXAvl5Nye8lxXOEK5R5Bi9YgepSbda3M2YxJAZESswQfbyzm6mHf1rX+tz2eByFl6ZmTSr3r6RSmN+Z+SHgk2lPryIXOHFH5K5uCFbtL03+OBpdu9FrwEbN1Z6pIbb8cHQW9Se+WjzndA8N1sJXEpdmN9cNruR8NZm3S0EY38=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Date", type date}, {"Comment", type text}, {"Desired Result", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Desired Result"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Comment", Splitter.SplitTextByDelimiter("[~accountid", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comment"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Comment", type text}}),
#"Added Index1" = Table.AddIndexColumn(#"Changed Type2", "Index.1", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "XPTO", each try Text.Range([Comment], 1,
Text.PositionOf([Comment], "]")-1)
otherwise null),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"XPTO", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"XPTO", Text.Clean, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Cleaned Text", {"XPTO"}, User, {"User"}, "User", JoinKind.LeftOuter),
#"Expanded User" = Table.ExpandTableColumn(#"Merged Queries", "User", {"Name"}, {"Name"}),
#"Sorted Rows" = Table.Sort(#"Expanded User",{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"XPTO"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Comment", Splitter.SplitTextByDelimiter("]", QuoteStyle.Csv), {"Comment.1", "Comment.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Comment.1", type text}, {"Comment.2", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "Text", each if [Name] = null then [Comment.1] else
[Name] & [Comment.2]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Comment.1", "Comment.2", "Name"}),
#"Sorted Rows1" = Table.Sort(#"Removed Columns2",{{"Index.1", Order.Ascending}}),
#"Added Custom2" = Table.AddColumn(#"Sorted Rows1", "Index_", each [Index] + ([Index.1] / 10)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom2",{{"Index_", type number}}),
#"Sorted Rows2" = Table.Sort(#"Changed Type4",{{"Index_", Order.Ascending}}),
#"Added Custom3" = Table.AddColumn(#"Sorted Rows2", "Custom", each Table.Group(#"Sorted Rows2", {"ID", "Index"}, {{"Valor", each Text.Combine([Text])}})),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Index.1", "Text", "Index_"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns3", {"ID", "Date"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Duplicates", "Custom", {"Index", "Valor"}, {"Index.1", "Valor"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Filter", each if [Index] = [Index.1] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([Filter] = 1)),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1", "Filter"})
in
#"Removed Columns4"

Ricardo

[NiugeS]

@camargos88 WOW!  I need to find some time to try this!  Thank you so much!  This is fantastic!

[camargos88]

Hi @NiugeS ,

I hope I it helped you, if possible, please mark this as an solution and kudos.

Thanks,

Ricardo

[NiugeS]

@camargos88 Will do and thank you.  I hope to have a look in the next few days..