Forum Discussion

Medmbchr's avatar
Medmbchr
Icon for Helper IV rankHelper IV
3 years ago

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 ...
Need Help
Adamboer's avatar
Adamboer
Icon for Responsive Resident rankResponsive Resident
3 years ago

Yes, it is possible to detect the columns that are used or not used in Power Query. Here are the steps you can follow:

  1. Load all the tables into Power Query.
  2. Combine all the tables into a single table using the "Append Queries" function.
  3. Use the "Remove Columns" function to remove any columns that you know are not needed.
  4. 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"}

  1. Expand the custom column to see which columns are used in the formulas.
  2. 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.