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,
Simple question. How to remove last two characters from each field of the column, i.e. replicate Excel's LEFT("column";LEN(column)-2) in M language?
Thanks!
Solved! Go to Solution.
Use Split
Right mouse on col, select split, then by char by position. Enter 2 and pick once from right. You'll get 2 columns so delete the one you don't want.
The M /Power Query code looks like this.
= Table.SplitColumn(#"Removed Columns1", "Field", Splitter.SplitTextByPositions({0, 2}, true), {"Field.1", "Field.2"})
Hi @FatherTheWizard,
As explained you can use this formula in M:
Text.Start([column];Text.Length([column])-2)
That means your column is a text and it is not empty or null otherwise you need to add a condition...
Hope it helps...
Ninter
Text.Length is the M equivalent to LEN, if you use that in combination with Text.RemoveRange you should be able to do what you want
Hi @FatherTheWizard,
As explained you can use this formula in M:
Text.Start([column];Text.Length([column])-2)
That means your column is a text and it is not empty or null otherwise you need to add a condition...
Hope it helps...
Ninter
@Interkoubess I stumbled across this post when trying to create a new column in power Query that remove the last digit of a string if it begins with 9.
For example 9396920 would be 939692 but 2020010 would remain the same.
I wrote this to identify if the column begins with 9.
Text.StartsWith([Custom],"9")
but when I try to conbine with the solution below, I get an error message.
Text.Start([column];Text.Length([column])-2)
Help would be much appreciated.
For anyone who found this and wonders why its an error; The original user uses semicolons instead of commas for formulas - Replace ; with , and it will work.
Use Split
Right mouse on col, select split, then by char by position. Enter 2 and pick once from right. You'll get 2 columns so delete the one you don't want.
The M /Power Query code looks like this.
= Table.SplitColumn(#"Removed Columns1", "Field", Splitter.SplitTextByPositions({0, 2}, true), {"Field.1", "Field.2"})
A bit clearer explanation:
inPower Query, use Split Column by Number of Characters. Enter 2 (or the number of characters you need to split) and pick "Once, as far right as possible".
Thank you for this answer. You saved me tons of time!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |