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))
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
Brilliant Thanks
Brilliant Thanks @Lz-3068
Just a few questions,
How does it know which column it is looking at? The column name is NumberExtract_Subject.
Does this create a new column? As there is already code in the NumberExtract_Subject column
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
OK, thanks
Will begin working on this again next week.
Many Thanks for your help.
@RhysWhite-1476. I should be there next week. Looking forward to hear from you
Take care in the meantime
4 people are following this question.