Python & Excel Files and Power Query

[uk_bryanmcguire]

I've run into a puzzling issue with my dataflow setup and I could really use some insights.

**Context:** I'm using Python to loop over some Excel files to rearrange column order and rename one or two columns. After these transformations, I have a dataflow ingest these updated Excel worksheets into Power BI. When I make the changes manually and upload the files, the dataflow processes them perfectly. However, when I use Python to make these same changes and then upload the files, the dataflow fails.

**Details:**
- The columns in both manually edited and Python-modified files are identical (at least visibly).
- I thought the issue might be due to the specific Python library, so I tried several (`openpyxl`, `pandas`, etc.), but they all lead to the same problem.
- I've double-checked column names, ordering, and even the types of changes being applied, but I haven't been able to figure out what is going wrong.

I'm truly stumped here. Are there any hidden properties or metadata in Excel that Power BI dataflows might be detecting differently between a manually-edited file and a Python-edited one? Any advice or suggestions on what might be happening under the hood would be greatly appreciated.

Thanks in advance!

[Anonymous]

Hi @uk_bryanmcguire ,

I tested it and it extracts/refreshes the data properly.

import os
import pandas as pd

folder_path = r'your_folder_path'

for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        df = pd.read_excel(file_path, engine='openpyxl')
        
        if 'A' in df.columns:
            df.rename(columns={'A':'NewA'}, inplace=True)
        
        new_columns_order = ['NewA', 'B', 'C']
        df = df[new_columns_order]
        
        df.to_excel(file_path, index=False)

print("Done")

I am using the folder connector. Could you provide more details and py code to help pinpoint the problem?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[uk_bryanmcguire]

Hi, Thanks for the input. Your method is sound however, the issue I have it once I pass the excel files into a fabric dataflow, the dataflow errors with the usual unhelpful message. If I then open and save the very same excel file, the dataflow refreshes without incident. Strange eh!!

[Anonymous]

Hi @uk_bryanmcguire ,

I think these excel files are generated by an external program/library that may be missing something, and opening it locally on close fixed it.

You might consider using the following PowerShell script automation to open and close these files sequentially:

$excel = New-Object -ComObject Excel.Application
$folderPath = "C:\path\to\your\folder" 

Get-ChildItem -Path $folderPath -Filter *.xlsx | ForEach-Object {
    $workbook = $excel.Workbooks.Open($_.FullName)

    $workbook.Close($false)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
}

$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel

 hope it helps.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

There is no such setting, but based on your change, you might provide a trach change table to use for following the changes and applying on the queries

[uk_bryanmcguire]

Just to add to the debate. I have just opened the Python generated excel file and simply saved it(same name) and now it is ingested with no issues!!