Not Monitored
Tag not monitored by Microsoft.
25,395 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
How do i convert the following to a Power BI Custom Query?
=IFERROR(MID(F2,FIND("499",F2,1),11),MID(F2,FIND("500",F2,1),11))
let
// Table for demo.
Source = Table.FromList(
{"Hello499Rhys890123","How are 500 you today?","500","Hope this makes sense"},
null, type table [String = nullable text]
),
Result = Table.AddColumn(Source, "Result", each
try Text.Range([String], Text.PositionOf([String],"499"), 11)
otherwise try Text.Range([String], Text.PositionOf([String],"500"), 11)
otherwise null,
type nullable text
),
// PQ Text.Range function does work the same as Excel MID function
MoreSecure = Table.AddColumn(Result, "MoreSecure", each
try Text.Range([String], Text.PositionOf([String],"499"), List.Min({Text.Length([String])-Text.PositionOf([String],"499"), 11}))
otherwise try Text.Range([String], Text.PositionOf([String],"500"), List.Min({Text.Length([String])-Text.PositionOf([String],"500"), 11}))
otherwise null,
type nullable text
)
in
MoreSecure
OK, thanks
Will begin working on this again next week.
Many Thanks for your help.
@Rhys White
How does it know which column it is looking at? The column name is NumberExtract_Subject
In my example I created/used a column named String. If your actual column name is NumberExtract_Subject then refer to [NumberExtract_Subject] instead of [String]
Does this create a new column? As there is already code in the NumberExtract_Subject column
The code I posted creates a new column, I had no choice as you provided nothing but an Excel formula
So, if your query somewhere creates column [NumberExtract_Subject] with some code, post that code if you need help to adapt what I posted
Hope this makes sense