Forum Discussion
Detect used columns
Hi
I think my question is quite silly but I will post anyways.
I have 30+ tables with 100+ columns in each table, due to the nature of the project we had to get all columns at first and then since we didn't know which columns to use at first.
Now that the project is at an end, I would like to rewrite my SQL code in data source so I can import only used columns. The exercise is hard since I need to check all formulas etc to know which columns have been used.
However, I am wondering if there is a way to "detect" only columns that are used or the inverse (detect columns that are not used) in Power Query or such.
Regards
3 Replies
Yes, it is possible to detect the columns that are used or not used in Power Query. Here are the steps you can follow:
- Load all the tables into Power Query.
- Combine all the tables into a single table using the "Append Queries" function.
- Use the "Remove Columns" function to remove any columns that you know are not needed.
- Add a custom column that uses the "Expression Dependency" function to detect which columns are used in the formulas. For example, you can use the following expression to detect if the column "Column1" is used in any formula:
Expression.Dependency([Column2]) = {#"Column1"}
- Expand the custom column to see which columns are used in the formulas.
- Remove any columns that are not used in any formula.
Alternatively, you can use the "Formula Firewall" feature in Power Query to detect any formulas that reference columns that are not present in the data source. This can help you identify any columns that are not used.
You can also use DAX Studio to connect to your published dataset (if you have Premium), and "View Metrics" and look at the Temperature column to see how much it is used.
Pat
