question

RhysWhite-1476 avatar image
0 Votes"
RhysWhite-1476 asked Lz-3068 commented

Converting an Excel Function to Power BI

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))

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 commented

@RhysWhite-1476

 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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

@RhysWhite-1476

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

0 Votes 0 ·
RhysWhite-1476 avatar image
0 Votes"
RhysWhite-1476 answered Lz-3068 commented

OK, thanks

Will begin working on this again next week.

Many Thanks for your help.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RhysWhite-1476. I should be there next week. Looking forward to hear from you
Take care in the meantime

0 Votes 0 ·