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.
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.
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.
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.
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;
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.
4 thoughts on “Search for a Column in the Entire Database with Table.ColumnNames in Power Query and Power BI”
This is perfect, I have a new data source with 1,000 tables and I need to recreate a report in Power BI. True needle in a hay stack probelm and this article was absolutely perfect. Many thanks.
I’ve used this before and LOVED IT! But today I’m getting the ‘We cannot convert a value of type Function to type Table’ error message. 🙁
Any ideas? Thank you!
probably because you have Function in your list too. Try to filter it by type to table only before searching
Thanks!! Exactly what i needed against a db here.