Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jracer007
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
v-chuncz-msft
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.

Stachu
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 🙂

With a custom column can I use PowerQuery?

Stachu
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 🙂

And now what do I do?
power bi5.png

Stachu
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 🙂

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"

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.