Replace Text with other text

[jracer007]

Hello community,

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

Original Text:


Desired text:



Thank and happy year!!

[v-chuncz-msft]

@jracer007,

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

[jracer007]

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

[v-chuncz-msft]

@jracer007,

Share us a more complete example, please.

[jracer007]

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



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


This is the data source that contains the search column:


And the visualization (matrix) is this:


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

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

[v-chuncz-msft]

@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.

[jracer007]

I do not understand.

[Stachu]

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

[jracer007]

With a custom column can I use PowerQuery?

[Stachu]

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 functionality

[jracer007]

And now what do I do?

[Stachu]

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

[jracer007]

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]

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?

[jracer007]

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