Error while combining files

[rahul_gupta]

Hell There, 

I have excel file which has data form column A to T. For each file Column A, B, Q,R,S, T will have same header. But for C to P header will change. Eg for January header will be 1st Jan 2022, 2nd Jan 2022 and so on.

For next month headers for C to P will be 1st Feb 2022, 2nd Feb 2022 and so on.

I am trying to combine this file in master file using power query. For few excel files it is working properly. But for few files its giving error 

DataFormat.Error: The document cannot be opened because there is an invalid part with an unexpected content type. [Part Uri=/xl/worksheets/sheet2.xml], [Content Type=application/xml], [Expected Content Type=application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml]. Details: [Binary]

When I checked content type for both files its same . please see below screen shot.

Both Files are saved with xlsx extension. Number of rows and columns are same in both files except header name form C to P changes.

ANy help to resolve the issue will be highly appriceated.

[v-angzheng-msft]

Hi, @rahul_gupta 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,
Community Support Team _ Zeon Zheng

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

[rahul_gupta]

@v-angzheng-msft  Applogies for delay reponse.

Yes my issue is resolved. I have modified my data files and resaved it to my source location. And then when i refreshed my power bi, it worked.

Thank you for your help and suggestion.

[Daryl-Lynch-Bzy]

okay you appear to be using some type of SharePoint list.  Is the m function:

SharePoint.Files ( URL , [ApiVersion=15] )

[Daryl-Lynch-Bzy]

Hi @rahul_gupta , please consider using the following steps to import the Excel files:

If the data from Excel Workbook is in a "Sheet" (i.e. not in a named "Table") and the data starts on the first row, add the "true" to import the worksheet with Headers Promoted.

Source = Excel.Workbook(#"Excel File", true)

With the inital table which contains "Known" columns "A, B, Q etc", you can now used the "Unpivot Other Columns" function by selecting these known columns.

#"Unpivot" = Table.UnpivotOtherColumns(#"Get Sheet", {"A", "B", "Q", "S", "T" }, "Attribute", "Value")

From here, you can transform the Other Column heading to Dates and format the values to the correct data types.

The above can then be converted into a useable function that can accept different Excel files or multiple worksheets.

[rahul_gupta]

Hello @Daryl-Lynch-Bzy 

Thank you for your quick response. The steps you have expalianed above is correct and using that i have created function. The issue i am facing is when i am trying to combine multiple files.

In my folder i have approx 59 files. And a new file is added every week. So this number will keep increasing.

When i am tryingto combine files then error pops up.

[Daryl-Lynch-Bzy]

@rahul_gupta - one approach I have tried in the past is to use SharePoint.Contents instead of SharePoint.Files.  This can offer more stability.  I also recommend a custom function to read the Binary file, this allows you to read the files one by one.  I normally use a Power BI Dataflow for these types of queries as well.

The function look like this:

(#"Excel Binary File" as binary) => 
let
   #"Buffer File" = Binary.Buffer( #"Excel Binary File" ),
   #"Open Excel" = Excel.Workbook( #"Buffer File" , True )
   #"Select Table or Sheet" = #"Open Excel"{0}[Data]
    blah blah
in
   Result

If you have lots of transformation, you have find it better to read the File into Dataflow table, then perform the transformation in second linked dataflow.   I.e. separate dataflow for Read and Transform, then Load the Dataset from the second dataflow.