Text.End Function Returning Blanks In PowerQuery

[rbowen]

I'm attempting to create a custom column in PowerQuery using the Text.End function. I'm trying to get the last 3 characters from each row in the budget_account_no column. The column is formatted as Text. The column gets created - with no errors - but all the rows are blank:

I'm using this command for the operation:

If I use the Text.Start function with some value, it works, probably because the first 4 characters are numbers. However, the last 3 characters are either three numbers, or a letter (both upper and lower case) followed by two numbers. Even though the entire column is formatted as text, my suspicion is that the mix of letters/numbers in the last 3 characters may be causing some trouble. 

Yes, I could just do this in DAX with the RIGHT function, but.....because PowerBI can't seem to recognize combinations of upper/lower case letters in situations like this, one has to go thru some gymnastics to get them to display correctly in the Table and Model views - hence the need to do this in PowerQuery. I need to make some critical relationships based on the correct Sub Accounts (the last 3 characters of budget_account_no column. Any ideas on how to get the Text.End function to cooperate in this case?

Thank you.

[v-heq-msft · Accepted solution]

Hi @rbowen ,
I’d like to acknowledge the valuable input provided by the @trebgatte .

Here's what I need to add:
According to your description, you are using the Text.End function to create a new column to get the last three characters. When there are spaces after the row data then the spaces are counted as characters when you use this function, which will most likely return blanks.
First, the first way is that you can create a custom function to clean the target data and then use Text.End to get the last three characters.

(textValue as text)=>

let
    //Split the text at each space character
    SplitText = Text.Split(textValue," "),
    //Remove the blank items from the list
    ListNonBlankValues = List.Select(SplitText,each _<> ""),
    //Join the list with a space character between each item
    TextJoinList = Text.Combine(ListNonBlankValues," ")
in
    TextJoinList

Final output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc67EcAgDAPQVThqCuEfUGYOjiL7LxFDCly4eWfJnjMbgArkkitbQ17lN8LrJlVxjR8+1vmanCwzj2Cie08tku0TRDEq3U1JLulpI7TwiU9KaacHuvP6AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [budget_account_no = _t, Budget_amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"budget_account_no", type text}, {"Budget_amount", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Custom.1", each Query1([budget_account_no])),
    #"Added Custom1" = Table.AddColumn(#"Invoked Custom Function", "Custom.2", each Text.End([Custom.1],3))
in
    #"Added Custom1"

The second way you can do this is by using the Split by position function, provided that you have the same characters at the beginning of each row of data

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[trebgatte]

Have you trimmed your column first to ensure there's no blanks? Also, are you using the Split button to create your Text.End?

[rbowen]

Hi trebgatte - 

Haven't trimmed anything for this table as it's coming straight out of the database. I realize that doesn't necessarily mean there aren't any blanks but usuallly I don't run into that. I can give it a try. Would blank spaces cause the Custom Column function I'm using above to create the new column with blank values?  I have used the Split function in the past, but only when the values have some kind of delimiter. I see now that it can also be used with other criteria as well. I'll give that a run too.