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

COS019970 — Tue, 09 Jul 2024 08:21:53 GMT

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:

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.
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

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

Anonymous — Wed, 10 Jul 2024 05:21:17 GMT

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.

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

COS019970 — Mon, 15 Jul 2024 09:20:59 GMT

Thank you so much!

topic Re: Help Needed with Formula Firewall Issue and Running Python Script in Power BI in Developer

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

Data Sources:

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

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