Help Needed with Formula Firewall Issue and Running Python Script in Power BI

[COS019970]

I'm encountering an issue with the Formula Firewall in Power BI when trying to combine data from multiple sources and run a Python script. I have two separate data sources that I want to combine and process using Python. Here are the details:

Data Sources:

1. Contract Mating:

   • This data source contains details about contract mating.
   • Sample fields: Dam, HERD_Dam, Returned, Year, Sire, AI_herd_Check, DIY_Herd_Check, Source.

2. Contract Mating Invoiced Sales:

   • This data source contains sales information for contract mating.
   • Sample fields: BTE Number, Item, Total DIY Quantity purchased, Average cost per straw Amount, Source.

This is the error message I am getting: Formula.Firewall: Query 'CombinedData' (step 'Run Python script') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

# 'dataset' holds the input data for this script
import pandas as pd
import numpy as np

# The dataset provided by Power BI should be directly accessible as a DataFrame named 'dataset'
data = dataset

# Verify the structure of the dataset
print(data.head())

# Split the combined dataset into 'Credit' and 'Contract' DataFrames based on the 'Source' column
Credit = data[data['Source'] == 'Contract Mating Invoiced Sales'].copy()
Contract = data[data['Source'] == 'Contract Mating'].copy()

# Bring back all the herds in the contract file
Contracts = Contract.reset_index(drop=True)

# Calculate the number of dams that received each sire recommendation per herd
Contract_dams_Herd_sire = Contracts.groupby(['HERD_Dam', 'Sire']).agg({'Dam': ['count']}).reset_index()
Contract_dams_Herd_sire.columns = ['HERD_Dam', 'Sire', 'No_of_Dams_Recommended_to_sire']
Contract_dams_Herd_sire['Max_number_of_straws_to_credit_per_sire'] = Contract_dams_Herd_sire['No_of_Dams_Recommended_to_sire'] * 2

# Calculate the number of dams recommended for matings in each herd
Contract_dams_herd = Contracts.groupby('HERD_Dam')['Dam'].nunique().reset_index()
Contract_dams_herd.columns = ['HERD_Dam', 'No_of_Dams_Contracted_to_herd']
Contract_dams_herd['Max_number_of_straws_to_credit_per_herd'] = Contract_dams_herd['No_of_Dams_Contracted_to_herd'] * 2

# Merge summary herd stats
Contract_summary = pd.merge(left=Contract_dams_Herd_sire, right=Contract_dams_herd, how='outer', left_on='HERD_Dam', right_on='HERD_Dam')

# Merge in herd stats with the sales figures from IT
Contract_Credit = pd.merge(left=Credit, right=Contract_summary, how='outer', left_on=['HERD_Dam', 'Sire'], right_on=['HERD_Dam', 'Sire'])

# Replace any sires who were contracted but have not been purchased to 0
Contract_Credit['Total DIY Quantity purchased'] = Contract_Credit['Total DIY Quantity purchased'].fillna(0)
# Replace any sires who were contracted but have no value to 0
Contract_Credit['Average cost per straw Amount'] = Contract_Credit['Average cost per straw Amount'].fillna(0)

# Calculate the maximum number of straws to credit based on purchases
def potential_straw_credit(x):
    if x['Max_number_of_straws_to_credit_per_sire'] >= x['Total DIY Quantity purchased']:
        return x['Total DIY Quantity purchased']
    else:
        return x['Max_number_of_straws_to_credit_per_sire']

Contract_Credit['Potential_Straws_to_credit'] = Contract_Credit.apply(potential_straw_credit, axis=1)

# Sort by the average cost of the straw per herd so that you end up crediting for the more expensive ones first
Contract_Credit = Contract_Credit.sort_values(by=['HERD_Dam', 'Average cost per straw Amount'], ascending=False).reset_index(drop=True)

# Cumulative sum the straws that were purchased off what needs to be credited
Contract_Credit['cumsum_herd'] = Contract_Credit.groupby(['HERD_Dam'])['Potential_Straws_to_credit'].cumsum()
Contract_Credit['previous'] = Contract_Credit.groupby(['HERD_Dam'])['cumsum_herd'].shift(1).fillna(0)

# Function to work out should the purchase be credited - any minus will be blanked as enough has been credited
def Cumsum(x):
    if x['cumsum_herd'] <= x['Max_number_of_straws_to_credit_per_herd']:
        return x['Potential_Straws_to_credit']
    elif x['cumsum_herd'] > x['Potential_Straws_to_credit']:
        return x['Potential_Straws_to_credit'] - x['previous']
    else:
        return 0

Contract_Credit['Straws_to_credit'] = Contract_Credit.apply(Cumsum, axis=1)
Contract_Credit['Straws_to_credit'] = np.where(Contract_Credit['Straws_to_credit'] < 0, 0, Contract_Credit['Straws_to_credit'])

# Calculate the amount to credit
Contract_Credit['Amount_to_credit'] = Contract_Credit['Straws_to_credit'] * Contract_Credit['Average cost per straw Amount']

# Summarize the total credit amount per herd
Contract_Credit_Per_Herd = Contract_Credit.groupby(['HERD_Dam']).agg({'Amount_to_credit': ['sum']}).reset_index()
Contract_Credit_Per_Herd.columns = ['HERD_Dam', 'Total_Credit_Amount (€)']

# Output final DataFrame to Power BI
result = Contract_Credit_Per_Herd

CombinedData TableCombinedData TableContract Mating Invoiced Sales TableCombinedData Table

[Anonymous · Accepted solution]

Hi  @COS019970 ,

This error occurs when the formula firewall is not functioning properly due to the structure of the query, you can check the related contentlink for the solution:

• Add references to the advanced editor to the query
• Ignore privacy level

Refer to:

Solved: Query references other queries or steps, so it may... - Microsoft Fabric Community

This is the related document, you can view this content:

Solved: Formula.Firewall: Query references other queries, ... - Page 2 - Microsoft Fabric Community

powerquery - Powerbi Formula.Firewall Error on loading python script - Stack Overflow

powerquery - Power Query - Please rebuild this data combination - Stack Overflow

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[COS019970]

Thank you so much!