Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rbowen
Helper I
Helper I

Text.End Function Returning Blanks In PowerQuery

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:

 

rbowen_0-1714592088858.png

 

I'm using this command for the operation:

 

rbowen_1-1714592172425.png

 

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.

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1714629896520.png

vheqmsft_1-1714629954967.png

vheqmsft_2-1714629971734.png

Final output

vheqmsft_3-1714630022021.png

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

 

vheqmsft_4-1714630155287.png

 

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

 

 

 

 

 

View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1714629896520.png

vheqmsft_1-1714629954967.png

vheqmsft_2-1714629971734.png

Final output

vheqmsft_3-1714630022021.png

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

 

vheqmsft_4-1714630155287.png

 

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
Most Valuable Professional
Most Valuable Professional

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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.