Replace Leading Characters for Multiple Columns at Once

[Axel_hnk]

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

[CNENFRNL · Accepted solution]

Simple enough,

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

[Axel_hnk]

That's what I was looking for! Thx!

[Vijay_A_Verma]

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]

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]

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.