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

Replace Text with other text

Hello community,

I want to request your help to replace the text of a Custom Column with the text of other column.

Original Text:
replace_1.PNG

Desired text:
replace_2.PNG


Thank and happy year!!

14 REPLIES 14
Community Support
Community Support

@jracer007,

 

You may try Power Query M function Text.Replace and Text.ReplaceRange, and DAX Function REPLACE Function and SUBSTITUTE Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Could you help me with the formula please? I am new at this.

@jracer007,

 

Share us a more complete example, please.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'll explain it to you in more detail, My data source comes from Google Analytics:

replacetext_1.PNG

Then I create a Custom Column to extract the data I need with a lookupvalue:
replacetext_2.PNG

This is the data source that contains the search column:
replacetext_3.PNG

And the visualization (matrix) is this:
replace_1.PNG

Now, what I need is for all that text to be replaced by this:

replacetext_4.PNG

Note: This text is in the same table as the replacetext_3 image.

@jracer007,

 

The first parameter <result_columnName> of LOOKUPVALUE Function needs to be the name of an existing column that contains the value you want to return.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I do not understand.

Community Champion
Community Champion

I still thik you should follow @Greg_Deckler idea and change it in PowerQuery. Instead of LOOKUPVALUE in DAX you can merge the tables using Table.NestedJoin in PowerQuery, it should give the same end result and you have much more flexibility in transforming the tables



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

With a custom column can I use PowerQuery?

Community Champion
Community Champion

no, you cannot use PowerQuery with DAX custom column - but you can create the same column in PowerQuery itself (so it's no longer calcuated), you can do the PowerQuery equivalent of DAX LOOKUPVALUE by using Merge Queries functionalityCapture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

And now what do I do?
power bi5.png

Community Champion
Community Champion

you select your lookup table from the drop down, create a left join (your lookup key needs to be unique), and then expand new column (the one you were looking up)

 

this video is very helpful on working with PowerQuery, may be good to :

https://www.youtube.com/watch?v=KXxUDWwo0pg

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

this is what I have in my advanced editor:

let
Source = GoogleAnalytics.Accounts(),
#"" = Source{[Id=""]}[Data],
#"UA" = #""{[Id="UA"]}[Data],
#"148099323" = #"UA"{[Id=""]}[Data],
#"Added Items" = Cube.Transform(#"148099323",
{
{Cube.AddAndExpandDimensionColumn, "ga:eventAction", {"ga:eventAction"}, {"Event Action"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddMeasureColumn, "Total Events", "ga:totalEvents"}
}),
#"Sorted Rows" = Table.Sort(#"Added Items",{{"Total Events", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Event Action] = "Billing History - Loaded" or [Event Action] = "Billing - Loaded" or [Event Action] = "Change invoice delivery form - Finished" or [Event Action] = "Saldos - Clicked" or [Event Action] = "Reload balance - Clicked" or [Event Action] = "Transfer balance - Loaded" or [Event Action] = "Buy button - Clicked" or [Event Action] = "Consumos - Clicked" or [Event Action] = "SMS Eventos - Loaded" or [Event Action] = "Internet details - Loaded" or [Event Action] = "Call Eventos - Loaded" or [Event Action] = "Other transactions - Loaded" or [Event Action] = "Buzón de Voz - Deactivated" or [Event Action] = "chat aquí - Clicked" or [Event Action] = "My information - Loaded" or [Event Action] = "Go cnfiguration Smartapps - Clicked" or [Event Action] = "View contract - Clicked" or [Event Action] = "Billing history - Loaded" or [Event Action] = "Billing - Loaded" or [Event Action] = "Download invoice PDF - Clicked" or [Event Action] = "Change invoice delivery form - Finished" or [Event Action] = "Add card form - Finished" or [Event Action] = "Internet Service - Viewed" or [Event Action] = "Change password wifi form - Finished" or [Event Action] = "Change name network form - Finished" or [Event Action] = "Phone Service - Viewed" or [Event Action] = "Consumption history - Loaded" or [Event Action] = "Television Service - Viewed" or [Event Action] = "zendesk-chat - Starts" or [Event Action] = "Pay invoice - Clicked" or [Event Action] = "Payment form - Finished")
in
#"Filtered Rows"

Super User IV
Super User IV

Looks like you are in query editor. Why not just remove your custom column, duplicate your desired column and rename the new column to your deleted column name?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




because the original column is created by LOOKUPVALUE from another column of more fields

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors