Replacing Text within each cell of a column

[martindoll]

I have been trying to get something to work all day.  In Transforming data in Power QUery, I need to replace vlaues.

Example:

Column1 has a combination of 85 possible values.

I need to go through each row of the column and Replace the value of each cell.

If I create 85 Replace Values, it exceeds the query limit.

I have tried about a dozen different scenarios I have searched for....none of them work for me.

Sample of the data

Column1

100000001, 100000002

100000001

100000001,100000002,100000003

I want

100000001 = Blue

100000002 = Red

100000003 = Yellow

So when it is done, it looks like:

Column1

Blue, Red

Blue

Blue, Red, Yellow

[v-cgao-msft · Accepted solution]

Hi @martindoll ,

Combines both of the above methods:

let
    ToRows = Table.ToRows( Parameter ),
    Outcome =
        List.Accumulate(
            ToRows,
            SourceTable,
            (x, y) =>
                Table.ReplaceValue(
                    x,
                    y{0},
                    y{1},
                    Replacer.ReplaceText,
                    {"Column1"}
                )
        )
in
    Outcome

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

[Omid_Motamedise]

You can solve this problem in several ways, whihc using List.Accumulate for running over the replaces pairs can be helpfull as follow.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSAAEMdBRjTSClWB0kClacDVwVnGSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Replace = Table.TransformColumns(Source,{{"Column1", each List.Accumulate({{"100000001","Blue"},{"100000002","Red"},{"100000003","Yellow"}},_,(a,b)=>Text.Replace(a,b{0},b{1})), type text}})
in
    Replace

 which results in 

If you need to know more about this function jucst see my video here

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

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSAAEMdBRjTSClWB0kClacDVwVnGSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
 dict = List.Buffer( Table.ToList(DimColor,(x)=>x)),
 f = (x)=> Text.Combine( List.Transform( Text.Split(x,","), (x)=> List.Skip(dict,(y)=>not Text.Contains(x,y{0})){0}?{1}?),","),
 to = Table.TransformColumns(Source,{"Column1",f})
in
to

[lbendlin]

Note that this will fail if there isn't a color for a certain data item.

Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSAAEMdBRjTSClWB0kClacDVwVnGSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Color", (k)=> Table.SelectRows(Replace, each [Value]=k[Column1])[Color]?{0}?,type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Index"}, {{"Column1", each Text.Combine([Color],", "), type text}})
in
    #"Grouped Rows"

Replace:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSAAEMlHSWnnNJUpVgdhKARUDAoNQVFzBgoFpmak5NfrhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Color = _t])
in
    Source

You may also need to wrap the Replace table in a Table.Buffer.

[martindoll]

Thanks for all the replies.....I needed to use the first suggestion because the parameters change and I needed it easy for end users to update....thanks to everyone.