Sometimes for tables with too many columns, and also for databases with too many tables, you do need a bit of help to explore the data. As an example; you know that you are looking for a column named “account status”, but the column does not exists in the accounts table. You need to search through all database tables for that column and find out which table has that value in it. Power Query has a great function that can help in such a scenario. Table.ColumnNames is a function that we are going to check out in this post. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

The Problem

You have a database with hundreds of tables or even more, and each table has many columns. You are looking for a specific column in the database and want to find out tables that such column exists in. Power Query has a function named Table.ColumnNames which gives you a list of all columns in a table as the output. Let’s see how this function works.

Sample Dataset

For this example; you can connect to the AdventureWorksDW database. Then select any of the tables to import data from the source database.

In the list of steps; you will find one step named as Navigation. This is the step that we have navigated to the table. Remove this step.

Then you will most probably see the list of all databases that you have access to (if you haven’t chosen a specific database at the connect to SQL Server section). Click on the “Table” in the Data row of the database of AdventureWorks2014 (or any other databases that you want to explore columns in it).

This action will give you the list of all tables under that database.

Now let’s assume we want to search for Account column in the entire database. Let’s see how this is possible.

Table.ColumnNames

The Table.ColumnNames function in Power Query will give you the list of all the columns in any given table. All you need to do is to call this function by passing the table as the input, and you will get a list of column names as the output. To use it in the existing example, click on Add Column, and then Add Custom Column.

In the Custom Column expression section; you can write the Table.ColumnNames function with the input parameter of Data (Data is the column that includes the data table). Please note that Power Query is case sensitive, and Table.ColumnNames should be written exactly as mentioned here in this blog post.

=Table.ColumnNames([Data])

This action will give you a new column with a list in every cell. This list is the list of column names for every table.

Now to get the list of all columns in all tables, you just need to expand this column;

After expanding, you will have all columns in all tables listed under this “Column” field. You can convert it to Text data type.

Search through Columns

Now that we got the list of all column names, then searching through it is very simple. you can use the basic search, but remember if you have more than 1000 columns in your data source, this will show you a limited list.

The best way to search into this list is using Advanced Search options. there you can choose criteria such as Contains, Equals, Begins With, or Ends With, and etc.

For example, a search for Contains… “Account” will end up with below result:

Be Careful of Case Sensitivity

Power Query is a case-sensitive language. There is a difference between “Account” as a text, and “account” as a text. One is using capital A, and the other one; lowercase a. To make sure you can always search for an item, regardless of the case sensitivity of that; you can first convert the column names all to lower case or upper case. To do that, select the “Column” field, and from the Transform tab, select transform to Lower.

now the whole column names list will be lowercase, and you can search through it only with lowercase values;

Summary

Sometimes simple transformations such as getting the list of columns from a table can be a big help for data exploration. In this post, you’ve seen how this can be helpful to search through all columns in a database. This approach can be used for any data sources, regardless if they are SQL Server databases or anything else. As long as the data source has table structure, then you can get the list of all columns from that table. This approach is particularly useful when you connect to databases with thousands of tables, and each table has hundreds of columns; CRM or Dynamics data sources is one of those examples.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.