Text.End Function Returning Blanks In PowerQuery

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