Functions in Power BI — (Table.FindText)

Functions in Power BI — (Table.FindText)

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.

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

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Explore your JIRA Data with Power BI

JIRA Software provides bug tracking, issue tracking, and project management capabilities for teams and organizations. The JIRA content pack for Power BI helps you quickly import JIRA data so you can get an instant dashboard to analyze workloads...

Managing your Data with Microsoft’s Power BI

In a world where the sheer amount of data is often overwhelming, the ability to interrogate and organise data to make meaningful business decisions is more important than ever. Microsoft have created Power BI to enable every day users to use the...

Tableau vs Power BI: Comparing the Data Visualization Tools

In analytics, Tableau is the leading visualisation tool. Its rich analytical features and attention to data details are the reason behind its popularity. Power BI, on the other hand, is preferred by professionals who are more comfortable with...

Use aggregate functions on rows in Power BI

Using aggregate functions on columns is fine, but doing the same on rows is — pure beauty! And all this with a single line of DAX code!

Power Query tips for every Power BI Developer

Use these simple yet powerful techniques to speed-up your Power BI development. If someone asks you to define the Power Query, what should you say?