cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiugeS
Helper V
Helper V

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 
  
UserName
User1John
User2Joe
User3Andrew
User4Mike
User5Sarah
User16Deb
User17Sam
User18Lucy

 

IDDateCommentDesired Result
123420/03/2020Thank you [~accountid:User1], I'll let [~accountid:User2] do the work.Thank you John, I'll let Joe do the work.
123422/03/2020No problem.  [~accountid: User4] Can you help?No problem.  Mike Can you help?
123424/03/2020Hey all - [~accountid:User4] Mike I can helpHey all - Mike I can help
123424/04/2020[~accountid:User1], [~accountid:User2], [~accountid:User3], [~accountid:User4] - all sorted!John, Joe, Andrew, Mike - all sorted
145619/03/2020Hey [~accountid: User1]Hey John
143520/02/2020Hey [~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 5
Lewis-H
Post Partisan
Post Partisan

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
Super User III
Super User III

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"

 

 

Capture.PNG

 

Ricardo

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors