Forum Discussion
Combining Text.End and Text.Length in the same custom column in power query (M Language)
Hi,
Say I have this simple table:
And I want to extract the right part of the 'Bank ID' column starting at the 6th character into a new column.
In Excel I would use
=RIGHT(Table[@[Bank ID]],LEN(Table[@[Bank ID]])-5)
I've tried doing the same with M but got an error,
can someone look at it and give me a fix:
4 Replies
First thing, what does the error say?
For debugging, comment out the LEN stuff and hardcode the value, so that you can start to simplify your code,
and can you repost the contents of the Advanced Editor window?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1MzE2MlTSUXJJzAOSFhamZrompobGxvElxUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, FN = _t, BankID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"FN", type text}, {"BankID", type text}}), #"Inserted Text Length" = Table.AddColumn(#"Changed Type", "Length", each Text.Length([BankID])-5), #"Added Custom" = Table.AddColumn(#"Inserted Text Length", "Custom", each Text.End([BankID],[Length])) in #"Added Custom"Well, here's the bad news, that worked great. It's possibly something to do with your data. Maybe you could try adding those last two lines to a different dataset. What error was returned at your side?
