Power BI: Easily Find a Column Name with this Function

One of the first steps in beginning a data analysis project is finding relevant tables to work with within a database. If you are inheriting a database with little to no documentation, finding these relevant tables can be tricky and time consuming, and typically involves us browsing through the column names of each table.

For example, I was recently working on a project where there were over 28 thousand tables in the database! Granted, most of them were empty. Still, I needed a way to make this easier.

How can we find a specific column name within the database?

One way is to use Table.ColumnNames, a function in Power Query. For an in-depth look at this function, check out this blog at radacad.

I wanted to make a more accessible method for doing this process. Just an easy copy and paste, as opposed to going through all those steps. To accomplish this, I created the following query:

(DataSource as table, FindColumn as text, optional ExactMatch as 
    logical) as table =>
// DataSource is the database that contains all the tables
// FindColumn is the column name to be searched for
// ExactMatch defaults to true and will do an exact match. False will //   do a loose match regardless of position or case.

let
    Source = DataSource,
    #"Removed Other Columns" = 
        Table.SelectColumns(Source,{"Name", "Data"}),
        
    #"Added Custom" = 
        Table.AddColumn(#"Removed Other Columns", "Columns", each
        Table.ColumnNames([Data])),
        
    #"Expanded Columns" = 
        Table.ExpandListColumn(#"Added Custom", "Columns"),
        
    #"Changed Type" = 
        Table.TransformColumnTypes(
            #"Expanded Columns",{{"Columns", type text}}),
            
    #"Filtered Rows" = 
        if ExactMatch = true or ExactMatch = null then 
            Table.SelectRows(
                #"Changed Type", each ([Columns] = FindColumn))
        else 
            Table.SelectRows(
                #"Changed Type", each
                (Text.Contains(Text.Lower([Columns]),
                Text.Lower(FindColumn))))
in
    #"Filtered Rows"

How To Implement

Simply create a new blank query, open the advanced editor and copy this code in.

New Source -> Blank Query

Open the Advanced Editor

Copy in the code

Next, you can pass in the parameters.

1. Just select your database (the query that contains the tables).

2. Type in the column name you want to search for.

Clicking Invoke will yield the following (new) query:

Loose Search

If you want to search for any match, regardless of position or case, type in 'false' for the ExactMatch parameter.

This will now yield the following query:

---

Potential Errors

If you get any errors, check the following.

1. The Data Source needs to contain columns for the Name and Data columns.

2. These columns need to be named exactly as "Name" and "Data".

Example:

Enjoy!