Unexpected Behavior when running Python at Power Query

[pedroEmb]

Hi there!

Nice to meet you. I'm experiencing an error while using Python in Power Query, and I simply can't figure out how to fix it.

I'm using the same Python kernel in Power BI Desktop that I use to run the script directly in Python. The code and settings are exactly the same. However, when I run it in Power Query, it throws errors that never occur when executing the script outside Power BI.

Initially, the errors were related to pd.concat. It seemed like Python was losing column orientation and mixing them up. So, I removed pd.concat from my code and worked only with dictionaries before converting to a DataFrame. That resolved the first issue, but now I'm getting the error message below:

Transcripted:

DataSource.Error: ADO.NET: ΡУţнǿń śçгĭφţ ēґѓог. <pi>ValueError: All arrays must be of the same length </pi> Detalhes: DataSourceKind=Python DataSourcePath=Python Message=ΡУţнǿń śçгĭφţ ēґѓог. <pi>ValueError: All arrays must be of the same length </pi> ErrorCode=-2147467259 ExceptionType=Microsoft.PowerBI.Scripting.Python.Exceptions.PythonScriptRuntimeException

Again, when I run the script directly in Python, everything works fine. I’ve confirmed that the API I’m consuming returns columns with consistent lengths.

Here’s the script I’m using:

import pandas as pd
import requests
from datetime import datetime


# Mantém como dict até o final
def getData(base_url, params):
    all_data = {}
    params["page"] = 1
    while True:
        response = requests.get(base_url, params=params)
        response.raise_for_status()
        data = response.json()
        items = data.get(list(data.keys())[0], [])
        if not all_data:
            for key in items[0].keys():
                all_data[key] = []
        for item in items:
            for key, value in item.items():
                all_data[key].append(value)
        if len(items) == 0:
            break
        params["page"] += 1
    return all_data

def execute_script():
    params = {
    "page": 1,  # starting page
    "filter": "{filter in json}",
    "method":"{api method}",
    "key": "{api key"
    }
    base_url = "{api endpoint}"
    # Extrai os dados como dicts para cada ano
    orc_dict_final = {}
    for year in [2025, 2024, 2023]:
        params['filter'] = f'{filter in json}'
        params['method'] = '{api method}'
        temp_dict = getData(base_url, params.copy())
        if not len(orc_dict_final.keys()):
            orc_dict_final = temp_dict.copy()
        else:
            for key in orc_dict_final.keys():
                orc_dict_final[key].extend(temp_dict[key])

    hh_dict_final = {}
    for year in [2025, 2024, 2023]:
        params['filter'] = f'{filter in json}'
        params['method'] = '{api method}'
        temp_dict = getData(base_url, params.copy())
        if not len(hh_dict_final.keys()):
            hh_dict_final = temp_dict.copy()
        else:
            for key in hh_dict_final.keys():
                hh_dict_final[key].extend(temp_dict[key])

    orc_dict_final = {key: orc_dict_final[key] for key in [{list of keys}] if key in orc_dict_final}.copy()
    hh_dict_final = {key: hh_dict_final[key] for key in   [{list of keys}] if key in hh_dict_final}.copy()

    for key in [{list of keys}]:
        orc_dict_final[key] = ['']*len(orc_dict_final['Data'])

    dict_final = hh_dict_final.copy()
    for key in orc_dict_final.keys():
        if key in dict_final:
            dict_final[key].extend(orc_dict_final[key])

    df_final = pd.DataFrame(dict_final)
    df_final = pd.pivot_table(df_final, values='Num', index=[col for col in df_final.columns if col not in ['Origem','Num']], columns='Origem', aggfunc='sum', fill_value=0)
    df_final.reset_index(inplace=True)
    return df_final

df_ano = execute_script()

If anyone has any hints or suggestions on how to resolve this issue, I’d really appreciate it!

[v-sdhruv]

Hi @pedroEmb ,

Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[v-sdhruv]

Hi @pedroEmb ,
Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?

Thank You

[v-sdhruv]

Hi @pedroEmb ,
Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?

Thank You

[BeaBF]

@pedroEmb Hi! The ValueError: All arrays must be of the same length in pandas almost always means that when you call pd.DataFrame(dict_final), one or more of the lists inside dict_final is a different length from the others. Outside Power BI you don’t see the error, but inside Power Query you do, which means something about the environment or the data returned is slightly different.

Try with:

import pandas as pd
import requests
from datetime import datetime

def getData(base_url, params):
"""
Fetch all paginated data into a dictionary of lists.
"""
all_data = {}
params = params.copy()
params["page"] = 1

while True:
response = requests.get(base_url, params=params)
response.raise_for_status()
data = response.json()

items = data.get(list(data.keys())[0], [])
if not items:
break

if not all_data:
for key in items[0].keys():
all_data[key] = []

for item in items:
for key, value in item.items():
if key not in all_data:
all_data[key] = []
all_data[key].append(value)

params["page"] += 1

return all_data

def safe_extend(dest, src):
"""
Extend lists in dest with lists from src, creating missing keys if necessary.
"""
for key in src:
if key not in dest:
dest[key] = []
dest[key].extend(src[key])

def execute_script():
params_template = {
"page": 1,
"filter": "{filter in json}",
"method": "{api method}",
"key": "{api key}"
}
base_url = "{api endpoint}"

# ---- Collect orc data ----
orc_dict_final = {}
for year in [2025, 2024, 2023]:
params = params_template.copy()
params['filter'] = f'{filter in json}'
params['method'] = '{api method}'
temp_dict = getData(base_url, params)
safe_extend(orc_dict_final, temp_dict)

# ---- Collect hh data ----
hh_dict_final = {}
for year in [2025, 2024, 2023]:
params = params_template.copy()
params['filter'] = f'{filter in json}'
params['method'] = '{api method}'
temp_dict = getData(base_url, params)
safe_extend(hh_dict_final, temp_dict)

# ---- Filter only needed keys ----
wanted_keys = [{list of keys}] # e.g. ['Data','Num','Origem',...]
orc_dict_final = {k: orc_dict_final.get(k, []) for k in wanted_keys}
hh_dict_final = {k: hh_dict_final.get(k, []) for k in wanted_keys}

# ---- Pad missing values so all lists equal length ----
def pad_dict(d):
max_len = max(len(v) for v in d.values()) if d else 0
for k in d:
if len(d[k]) < max_len:
d[k].extend([''] * (max_len - len(d[k])))

pad_dict(orc_dict_final)
pad_dict(hh_dict_final)

# ---- Merge dictionaries ----
dict_final = {}
safe_extend(dict_final, hh_dict_final)
safe_extend(dict_final, orc_dict_final)

# ---- Check consistency ----
lengths = {k: len(v) for k, v in dict_final.items()}
if len(set(lengths.values())) != 1:
raise ValueError(f"Inconsistent column lengths detected: {lengths}")

# ---- Create DataFrame and pivot ----
df_final = pd.DataFrame(dict_final)
df_final = pd.pivot_table(
df_final,
values='Num',
index=[col for col in df_final.columns if col not in ['Origem','Num']],
columns='Origem',
aggfunc='sum',
fill_value=0
)
df_final.reset_index(inplace=True)
return df_final

df_ano = execute_script()

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[pedroEmb]

Perfect! I will check it out