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.
I'm an M newbie and have been struggling with the most efficient way to solve a problem. Three of the columns in a data source table have some values with an extra comma at the end. For example, the "Product" column will have some records that say "Product A" and other records with "Product A," (notice the trailing comma).
I see that the Text.TrimEnd function can help remove the extra comma. For now, I'm creating a new column with the trimmed text using:
= Table.AddColumn(#"Changed Type", "Test", each Text.TrimEnd([Product],","))
This works. Is there any way to use that function without having to create a new custom column? I'd like to just transform the values in the existing column.
Solved! Go to Solution.
You could just delete the original column after creating your new one, then rename the new column - simple, but effective and readable.
Or if the commas ONLY appear at the end (e.g. "Fish, and Chips" can be changed to "Fish and Chips" try:
= Table.ReplaceValue(#"Changed Type", ",", "", Replacer.ReplaceText,{"Product"})
Alternately, try something arcane like below which ONLY removes one comma at the end:
= Table.TransformColumns(#"Changed Type", {{"Product", each if Text.EndsWith(_, ",") then Text.Start(_, Text.Length(_)-1) else _}})
You could just delete the original column after creating your new one, then rename the new column - simple, but effective and readable.
Or if the commas ONLY appear at the end (e.g. "Fish, and Chips" can be changed to "Fish and Chips" try:
= Table.ReplaceValue(#"Changed Type", ",", "", Replacer.ReplaceText,{"Product"})
Alternately, try something arcane like below which ONLY removes one comma at the end:
= Table.TransformColumns(#"Changed Type", {{"Product", each if Text.EndsWith(_, ",") then Text.Start(_, Text.Length(_)-1) else _}})
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.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |