Forum Discussion

synaptical's avatar
synaptical
Helper I
11 months ago
Solved

Power Query Error when importing Excel files after formatting a table area in excel

Hi,   I have a power bi file that reads several excel files from a folder. In the excel files, we have several columns, some are optional to use and might be empty if not needed (except row 1 which...
Need Help
  • Poojara_D12's avatar
    Poojara_D12
    11 months ago

    Hi synaptical 

    This issue occurs because when you converted your Excel range into a formatted table, Power BI no longer recognized the old column structure it was originally mapped to. In your query, Power BI is still looking for a placeholder field like Column1, but now the actual table header is Title, which causes the error. You don’t need to rebuild your whole transformation; instead, open the Power Query Editor and adjust the step where the error appears (usually “Promoted Headers” or “Changed Type”) by replacing Column1 with Title. If you are combining multiple files from a folder, make sure the sample file query is corrected, as Power BI applies those steps to all files. Going forward, keep header names consistent in your Excel templates, and in Power Query use the “Choose Columns” step with the dynamic option enabled so that missing optional columns don’t break refreshes. This way, you only fix the column reference once, and all future files will load smoothly without recreating your transformations.

     

hnguy71's avatar
hnguy71
Super User
11 months ago

Hi synaptical 

There is a way to return all columns (both mandatory and optional) and then be able to import them all together. It does require a small amount of PowerQuery magic but it's not impossible. 

 

Do you have some sample data?

EDIT: Here's a sample of how to bring in all columns. I've included comments for you to understand what's going on. For example:

 

  • synaptical's avatar
    synaptical
    Helper I
    11 months ago

    Hi

    Thank you very much for your support.

     

    I hope I get your solution approach: I have to insert a step in Power Query named "CustomExpand".

     

    I tried it and Power BI says "The column 'Transform File' of the table wasn't found." I adusted the query as follows:

     

    = Table.ExpandTableColumn( #"Removed Other Columns1", "Transform File", Table.ColumnNames(Table.Combine( #"Removed Other Columns1"[#"Transform File"])) )

    Unfortunately, I have no proper sample data as my sources are classified to be used whithin the company.

     

    Please consider the import of several file worked fine before I did this: It broke when I adjusted in excel (selected columns and rows and marked them as a table). And I am currently testing with one excel in the data source folder, not several as usual.


    Could you help me anyway?

    • hnguy71's avatar
      hnguy71
      Super User
      11 months ago

      Hi synaptical 

      There are two areas in which you need to do the changes.

       

      1. From your "Transform Sample File" default helper query. Make sure you removed the change data type step from there:
      2. The "Transform File" is a column reference. while "Removed Other Columns1" refences my previous applied step. Without seeing your code I'm not sure where you've placed it.

       

      Can you open up the advanced editor and copy and paste the entire query here?