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.
44,037 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lz._ 9,016 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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.