Column Dependencies/Usage Within Applied Steps

[ACraig08]

Hello, I am not even sure if this is going to make sense or not but I will do my best to explain it. I have a table that pulls in data from an Oracle database. I have applied a bunch of steps to it creating new columns and changing data types, the usual. I am trying to go back through it and clean it up and even see how I can do some of these things on the Schema side and not within Power BI. I am realizing that I can't remember where certain columns I creataed are used. By that I mean, used within the calculation of another column, or even within another table that references the primary table. 

I was wondering if anyone knew of a way that I can generate some kind of report or depenencies tree or anything that would break this down for me. Show me where each column is being used within the Applied Steps. I already found a Power BI Field Finder pbix that helps me with when the fields are used within an actual visualization or filter. But I need something like that for the Applied Steps/ Created Columns.

Also, even if it is something creative like exporting the M code from Advanced Editor and using another coding language I am more than willing to do that!

[ZhangKun]

Congratulations on solving the problem.
Reading column dependencies through DAX and M formulas are two different difficulty problems. Regardless of the scheme, you can only read the result table and the M formula, and even if you use the M engine to execute the query, you will not be able to find the column dependencies correctly. There are many operations that affect the result, such as adding columns, renaming, and converting tables to records.

[Nasif_Azam]

Hey @ACraig08 ,

You're raising an excellent and very real challenge when managing complex Power Query M transformations in Power BI: tracing the lineage and dependency of custom columns across applied steps. Since Power BI doesn't offer a built-in "column dependency tree" like it does for field usage in visuals

Understanding Dependencies in Power Query Applied Steps
Power BI’s Applied Steps in Power Query Editor operate sequentially. Every new column or transformation depends on the result of the previous step. Unfortunately, there’s no visual dependency graph for columns or calculations. However, you can reverse engineer it.

Solutions

1. Export and Analyze M Code Manually

• In Power BI Desktop, go to Home > Advanced Editor in Power Query.
• Copy the full M script.
• Save this to a .txt or .m file.

You can now use external tools to parse and analyze this code.

2. Use a Code Parser (like Python or VS Code Search)

import re

with open('your_query_file.m', 'r') as file:
    m_code = file.read()

# Find column creation lines
created_columns = re.findall(r'AddColumn\(([^,]+), "([^"]+)",', m_code)
print("Created Columns and Source Steps:")
for step, col in created_columns:
    print(f"Column '{col}' created in step based on: {step}")

You can also:

• Search for each custom column name using col_name to see where it's referenced in later steps.

• Use dependency detection logic: look for #"Previous Step"[ColumnName] or [ColumnName] in each expressions.

3. Use Power BI Documenter Tools

These tools analyze PBIX files and sometimes provide deeper lineage:

• Power BI Helper : Shows column and measure dependencies.

• DAX Studio : Mainly for DAX, but helps with understanding semantic layer.

• PBI Explorer (community tool) :Paid, but supports deeper documentation including M code analysis.

4. Create Your Own "Dependency Graph" in Excel

You can build a spreadsheet with:

• Column Name

• Step Where Created

• Referenced In Steps

• Used In Measures/Visuals

Then use formulas or Power Query inside Excel to cross-check dependencies.

Summarize Approaches:

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[ACraig08 · Accepted solution]

It seems silly to reply to my own post but I thought other people might find the solution I found interesting. I was able to do it using Python coding by extracting the information from the M code using regex! 

# Import required packages
import re
import pandas as pd

# Create the variable that contains your M code
m_code = '''

    Insert your M code here

'''

# Regular expression to find all Table.AddColumn occurrences
add_column_pattern = r'Table\.AddColumn\([^)]+, "([^"]+)", each ([^)]+)\)'
matches = re.findall(add_column_pattern, m_code)

# Collect extracted dependencies
data = []
for column_name, column_expression in matches:
    # Find all references inside square brackets []
    dependency_pattern = r'\[(.*?)\]'
    
    dependencies = re.findall(dependency_pattern, column_expression)
    
    # Append results to data list
    data.append({'Column_Name': column_name, 'Dependent_On': ', '.join(set(dependencies))})

# Convert to Pandas DataFrame
df = pd.DataFrame(data)

# Display extracted dependency table
print("Extracted Dependencies Table:")
print(df.to_string(index=False))

# Export to Excel
output_file = "column_dependencies.xlsx"
df.to_excel(output_file, index=False)

print(f"Extracted Dependencies Table saved to {output_file}")