Forum Discussion
Replace text within value based on another column
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.
5 Replies
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
camargos88 WOW! I need to find some time to try this! Thank you so much! This is fantastic!
Hi NiugeS ,
I hope I it helped you, if possible, please mark this as an solution and kudos.
Thanks,
Ricardo
