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.
Hi All,
I have a named range, that I would like to pull a partial text string from.
The Cell A4 is:
Current Weeks : 'From: 2020 PD 09 WK 1 (33) to 2020 PD 09 WK 4 (36)`
I currently have:
let
Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Middle([Column1],29)),
But I've been Googling for 2 days. I've tried to add
text.length
text.end
and can't figure out how to take off the the apostrophe at the end.
Any advise?
Solved! Go to Solution.
Hi @Anonymous
Please try this, I've tested it and it works on the string you provided. What makes this tricky is that the last character in the string is not an apostrophe,it's a back tick ` The character under the tilde key which is at the top left of my keyboard.
let
Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
Substr = Table.AddColumn(Source, "Substring", each Text.Middle([Column1] , Text.PositionOf([Column1], ":", 1) + 1 , Text.PositionOf([Column1], "`") - Text.PositionOf([Column1], ":", 1) - 1))
in
Substr
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi @Anonymous
Please try this, I've tested it and it works on the string you provided. What makes this tricky is that the last character in the string is not an apostrophe,it's a back tick ` The character under the tilde key which is at the top left of my keyboard.
let
Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
Substr = Table.AddColumn(Source, "Substring", each Text.Middle([Column1] , Text.PositionOf([Column1], ":", 1) + 1 , Text.PositionOf([Column1], "`") - Text.PositionOf([Column1], ":", 1) - 1))
in
Substr
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
@Anonymous , you may extract the substring this way,
let
Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
Added = Table.AddColumn(Source, "SubStr",
each [
positions = Text.PositionOfAny([Column1], {"'", "`"}, Occurrence.All),
substr = Text.Range([Column1], positions{0}+1, positions{1}-positions{0}-1)
][substr]
)
in
Added
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous , based on your mockup data, it's easier to change
substr = Text.Range([Column1], positions{0}+1, positions{1}-positions{0}-1)
to
substr = Text.Range([Column1], positions{0}+7, positions{1}-positions{0}-1)
to get the desired substring.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I copy and pasted your code, and the SubStr came almost perfect. However I don't want the "From:", Just the Date string.
Should I replace the:
positions = Text.PositionOfAny([Column1], {"'", "`"}, Occurrence.All),
With
positions = Text.PositionOfAny([Column1], {":", "`"}, Occurrence.All),
Also, the results in the Custom Column, just say Table..?
@camargos88 Sorry!
=let
Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each "Latest 4 Wks - Ending:" & " " & Text.Middle([Column1],29, Text.Length([Column1])-2)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
Custom = #"Removed Columns"{0}[Custom]
in
Custom
@camargos88 I tried to adjust the N number for the end, and no matter what number I use, it still does not eliminate the apostrophe...?
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 |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |