none
How to delete duplicate characters from a string (eg. xzxxxzzzzxzzzzx-> leave only xz) in Power Query? RRS feed

  • Question

  • Hello,

    I have a huge table with text in cells that consist from multiple 'x's and 'z's. I want to deduplicate values so I have one x and one z only.

    For example:

    xzzzxxxzxz-> xz

    zzzzzzzzzz-> z

    The table is very big, so I don't want to create additional columns. Can you please help?


    Tuesday, September 3, 2019 4:15 PM

Answers

  • One possible way (Aleksei Zhigulin's method)... but there are many other ways.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0, (x) => Text.Combine(List.Transform({"x","z"}, each if Text.Contains(x[data], _, Comparer.OrdinalIgnoreCase) then _ else "")),(a,b,c) => c,{"data"})
    in
        #"Replaced Value"

    Aż się boję pisać by nie być ukąszonym ;-)))

    Tuesday, September 3, 2019 6:11 PM
  • Another way (if you need just deduplicate any values in each row of the column):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        transform = Table.TransformColumns(Source, {"data", each Text.Combine(List.Distinct(Text.ToList(_)))})
    in
        transform

    Wednesday, September 4, 2019 11:24 AM

All replies

  • One possible way (Aleksei Zhigulin's method)... but there are many other ways.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0, (x) => Text.Combine(List.Transform({"x","z"}, each if Text.Contains(x[data], _, Comparer.OrdinalIgnoreCase) then _ else "")),(a,b,c) => c,{"data"})
    in
        #"Replaced Value"

    Aż się boję pisać by nie być ukąszonym ;-)))

    Tuesday, September 3, 2019 6:11 PM
  • Another way (if you need just deduplicate any values in each row of the column):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        transform = Table.TransformColumns(Source, {"data", each Text.Combine(List.Distinct(Text.ToList(_)))})
    in
        transform

    Wednesday, September 4, 2019 11:24 AM
  • Thank you guys for help!
    Monday, September 9, 2019 11:17 AM