Find and replace in M code

[psyche2k]

I have a 2 tables.

First table contains column "PI Tags"
And 2nd table contains columns "APC_KPI" and "KPI Standard Name"

Now I need to find the text present in the "APC_KPI" column inside "PI Tags" column and then replace it with the corresponding value from "KPI Standard Name" column.

I basically want this alterxy functionality in Power Query.

[DmitryKo]

Aren't you going to parse this (probably comma separated or similar) column with a list of tags into something like multiple rows, so that there's one row for each combination of tag and original entry? I suppose so, as tags are usually pulled as a dimension into the model, for later filtering.

In that case, it could be more efficient to just inject your "KPI Standard Name" values as an attribute in that dimension, joining based on tag name (APC_KPI)

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[psyche2k]

I've posted it here:

https://community.fabric.microsoft.com/t5/Desktop/Find-and-Replace/m-p/3511846#M1160511

[lbendlin]

Here's a lazy version for the PI Tags table transform.  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZPRaoMwFED/pc9DymDbs4sIBRPLbN2glJHVtLslTYqmjv39QmGNbInePAl6jpqbnM1mllGSrO8f5vb6ni5JstKGS6Zp3SUkLa73Ztu7MS4HxWUU/PbCv0pW5nnAqorngmVXM22Ol86IhmhlWi2laCPF9dnASSD4noPkHyDBfI+tKKBQri7WoQu0sWxFI/agRDP9pUx0cLDkYHMmvkTrhapE28MOsXZaM22I5Kcz5mdo/Srg8GlnGztjZ9oDgJVKVq2q4NOIF92Gl+4M9DECejQ3I2L4k8F5WeQRjQnv8ek314jo/kvh7RiwmNj8eCg0Pz0emXMwgTl6Yn+H4HhYQxIflc8a7cAJf2NyT5AvQETkgVEjwMXj4b3h+DjEUUMEs/0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PI Tags" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [PI Tags]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Custom.1","Custom.2","Custom.3","Custom.4"}),
    #"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"Custom.3"}, KPI, {"APC_KPI"}, "KPI", JoinKind.LeftOuter),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Queries",each [PI Tags],each if [Custom.4] = null then [PI Tags] else Text.Replace([PI Tags],"." & [Custom.3] & ".","." & [KPI]{0}[KPI Standard Name] & "."),Replacer.ReplaceValue,{"PI Tags"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"PI Tags"})
in
    #"Removed Other Columns"

This can be done shorter but at the expense of readability.

[lbendlin]

Here is the short version

#"Replaced Value" = Table.ReplaceValue(
    Source, 
    each [PI Tags], 
    each 
      let
        f = Text.Split([PI Tags], ".")
      in
        if List.Count(f) < 4 then
          [PI Tags]
        else
          Text.Replace(
            [PI Tags], 
            "." & f{2} & ".", 
            "." & Table.SelectRows(KPI, (k) => k[APC_KPI] = f{2}){0}[KPI Standard Name] & "."
          ), 
    Replacer.ReplaceValue, 
    {"PI Tags"}
  )