Converting an Excel Function to Power BI

Rhys White 1 Reputation point

How do i convert the following to a Power BI Custom Query?


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

    @Rhys White

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

  2. Rhys White 1 Reputation point

    OK, thanks

    Will begin working on this again next week.

    Many Thanks for your help.