Extract all characters EXCEPT the last X amount

[Anonymous]

Hello All:

After lurking for months I finally felt compelled to ask a question...

I have 10 Columns that include 22 characters towards the end, so I am trying to extract everything except the last 22 characters. I can't use the Transform tab's Extract feature because it only gives options to extract the first x amount of characters. 

I tried to hack it like you can in excel's LEFT/RIGHT functions by using a negative number, but Power BI does not allow that functionality in M Language. 

The way around this I've found is tedious, but I'm afraid it would make my query too bloated.

1) Add 10 new columns, all doing a Text.Reverse() on their values,

2) run the Extract for 22 characters

3) Add another 10 columns to Text.Reverse() to undo the initial reverse. This gives me the end value I wanted.

4) Delete 10 added columns as well as the 10 original columns

I feel like I should be able to do this by transforming the columns instead of adding 20 new ones, but I couldn't figure out how to run the Text.Reverse (Nested inside an If Then Else statement) on multiple columns. I tried writing a statement using Table.TransformColumns instead of TableAddColumn but either it's not possible or I was doing it wrong. 

Is this really the most efficient way to do this?

CC: @Anonymous 

[Anonymous]

there is now the option to extract the N First/Last characters: 

In PowerQuery:  Add column tab > Extract dropdown menu > first or last characters > enter the number of characters to "isolate"

Otherwise you can also: 

In PowerQuery: Split column > by number of characters > enter the number of characters to remove from your column, on the split menu select to split starting from the left or right. 

[alexvc]

Hi there,

This is most efficient way I can think of doing it:

Measure = LEFT(Column, LEN(Column)-22)

Let me know if it works,

Alejandro

[ChrisMendoza]

@Anonymous -

Are you wanting something like:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs7PTVUoLilNS1MwNDI2MTUzt7A0QGYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([col1],Text.End([col1],22),""))
in
    #"Added Custom"

[Anonymous]

Yes! This would half solve the issue... the other part I want is for it to transform the existing column instead of creating a new one.

Thanks!

[ChrisMendoza]

@Anonymous -

Option 1 is to remove the column that is no longer needed.

Option 2 is to learn more about the functions 🙂

From my solve in a prevous post @ https://community.powerbi.com/t5/Desktop/New-column-with-contents-and-complete-with-0-on-left-side/m-p/668962#M321809

Modified to your situation:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs7PTVUoLilNS1MwNDI2MTUzt7A0QGYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [col1],each Text.Replace([col1],Text.End([col1],22),""),Replacer.ReplaceValue,{"col1"})
in
    Custom1

[BekahLoSurdo]

Hi @Anonymous,

In a Custom Column, use Text.End:

Text.End("abcd", 2) equals "cd"

Does that help?

Bekah

[Anonymous]

Thank you, but I think you misunderstood me. I know my question was kind of drawn out so I apologize.

That solution is going to give me the last 22 instead of removing them. Text.End is the function that is used by going to Transform->Last Characters...

Thanks for looking into this

[BekahLoSurdo]

I'm sorry I misunderstood. Try this:

= Text.Start( [Column], Text.Length( [Column] ) - 22 )

[Anonymous]

Thanks for the help, I have a feeling this is close, but once again I run into the issue of Power BI not allowing negative values in the Text.Start function