I’ve always been amazed at the simple find function. You can find anything that matches your criteria, even if your search item is hidden in different work sheets, open text or tables.

Just imagine, you can search for anything that appears anywhere in your raw data. Even if it is hidden somewhere in an open text field. Quite amazing!

How is this helpful? It’s very helpful if you are looking for a key word or a set of numbers that has importance, but you just don’t know where to look. This number can be in the open-text field, it could be in the name field etc.

Once found, you can combine these search results and tell a story with it. Sometimes, doing a find is just out of curiosity, but sometimes you may be surprised by what you’ve found.

I’ve used this Table.FindText function as a fraud detection exercise, It came in very handy because my tables were huge and I was just looking for one single individual. There are other ways this simple function can be helpful. You can use it as a historical search of account records and to answer some questions you may have.

Let’s get started!

Let’s take a look at the Table.FindText function on Microsoft.

In plain English, this is a function that uses a table as input and searches for an item within that table. It then returns a table with the rows that matches your criteria.

Let’s try this out.

Getting the Tables

The first step is tricky — maybe you are lucky to have a column of tables already set up for you, but I have to convert my binary files into table format from a folder.

It’s not that difficult and I use this function.

(FOLDER_PATH , FILENAME ) =>
let
    Source = Csv.Document(File.Contents(FOLDER_PATH&FILENAME)),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
   #"Promoted Headers"

If you connect Power BI using the folders option and have a list of binary files, you can convert these binary files into tables using the above function.

To add in a new function, simply go to Power Query > New Blank Query > Advanced Editor >** Paste in the script**

The **FOLDER_PATH **and **FILENAME **parameters in this query are the columns Power Query prepared for you once you connect to it.

#power-bi #functional-programming #data-science #data-analysis #technology

Functions in Power BI — (Table.FindText)
17.25 GEEK