Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Simple enough,
= List.Accumulate({"col1","col2",..."coln"},#"Previous Step",(s,c)=>Table.TransformColumns(s, {c, each Text.TrimStart(_,"0")}))
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Simple enough,
= List.Accumulate({"col1","col2",..."coln"},#"Previous Step",(s,c)=>Table.TransformColumns(s, {c, each Text.TrimStart(_,"0")}))
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
That's what I was looking for! Thx!
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"
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.