Converting an Excel Function to Power BI

Rhys White 1 Reputation point
2021-07-16T08:54:38.343+00:00

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

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,606 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2021-07-16T10:24:00.357+00:00

    @Rhys White

    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. Rhys White 1 Reputation point
    2021-07-16T12:12:01.603+00:00

    OK, thanks

    Will begin working on this again next week.

    Many Thanks for your help.