Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
@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"
Proud to be a Super User!
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!
@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
Proud to be a Super User!
Hi @Anonymous,
In a Custom Column, use Text.End:
Text.End("abcd", 2) equals "cd"
Does that help?
Bekah
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
I'm sorry I misunderstood. Try this:
= Text.Start( [Column], Text.Length( [Column] ) - 22 )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |