Forum Discussion

martindoll's avatar
martindoll
Frequent Visitor
1 year ago
Solved

Replacing Text within each cell of a column

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

General Comment
  • Anonymous's avatar
    Anonymous
    1 year ago

    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 

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

     

    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's avatar
      martindoll
      Frequent Visitor
      1 year ago

      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.

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    1 year ago

    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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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