Forum Discussion

ACraig08's avatar
ACraig08
Helper III
1 year ago
Solved

Column Dependencies/Usage Within Applied Steps

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 c...
Need Help
  • ACraig08's avatar
    ACraig08
    1 year ago

    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}")

     

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

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:

 

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