Forum Discussion

Axel_hnk's avatar
Axel_hnk
Frequent Visitor
4 years ago
Solved

Replace Leading Characters for Multiple Columns at Once

Hi there,
I have always a couple of columns in my data source where the data has leading (in my case) zero. I get rid of them by:

Table.TransformColumns(#"StepBefore", {{"ColumnName", each Text.TrimStart(_, "0"), type text}})

This is somewhat cumbersome when you have 15 or so of them for each table.

 

Is there a way to apply this logic to multiple columns with one PQ step like I can do that with Table.ReplaceValue?

 

Thx in advance

Axel

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    Simple enough,

    = List.Accumulate({"col1","col2",..."coln"},#"Previous Step",(s,c)=>Table.TransformColumns(s, {c, each Text.TrimStart(_,"0")}))

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    Simple enough,

    = List.Accumulate({"col1","col2",..."coln"},#"Previous Step",(s,c)=>Table.TransformColumns(s, {c, each Text.TrimStart(_,"0")}))
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Select all those columns and unpivot them. Then run Table.Replace and then pivot them back again.

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Column1", "Index"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns",each [Value],each(Text.TrimStart([Value],"0"))
,Replacer.ReplaceText,{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

     

     

     

    • Axel_hnk's avatar
      Axel_hnk
      Frequent Visitor
      4 years ago

      Hi,
      I guess this only works for a whole table.

       

      But I need to replace the leading character for just some columns, not all.

       

      Thx anyway.

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        No, it will work only for selected columns. In the example above, I have selected only columns 2 to 5...I have left column1. 

        If you see the result, column 1 in 2nd entry has a leading 0 which is not replaced.