Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |