Forum Discussion
Replace Leading Characters for Multiple Columns at Once
Simple enough,
= List.Accumulate({"col1","col2",..."coln"},#"Previous Step",(s,c)=>Table.TransformColumns(s, {c, each Text.TrimStart(_,"0")}))
Most Valuable ProfessionalSelect 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"
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.