Error when combining excel files from sharepoint

[Yass91]

Hi,

I have an issue every time a new excel file is added to my Sharepoint folder. My Power BI automatic update crashes when a new file is added in the Sharepoint folder.

In Power Query, I found that when the excel files are loaded, the most recent one has no Table loaded but an Error (photo below).

I need to combine the files of the folder every week, and every week an additional excel file has to be integered to my Power BI report.

How can I solve this issue to avoid having this issue every week?

Thanks!

[jennratten · Accepted solution]

Hello @Yass91  - you are getting this error because your have a step in the transform sample file query that explicitly mentioning a column named CLIENT. It must exist in the file used as the sample file but it is not present in the new file.  This is why you get the error when the function is invoked.  If the column is not needed, you can edit the transform sample file query to eliminate the reference to the CLIENT column.  If the column is needed then you must add it to the new files.

[v-echaithra]

Hi @Yass91 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra]

Hi @Yass91 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Yass91 ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithra.

[Omid_Motamedise · Accepted solution]

It might be caused due to the formula you have wrtien in the custom column.
you should convert the Binary file into table using the following fomrula first then can handle some cleaning process

Excel.Workbook([Content], null, true)

[daircom]

@Yass91 ,
Can you specify what error occurs every time? 

[Yass91]

Hi @daircom,

I just have an Error in the column "Transform file", when the Transform file function is invoked.

Then, the error mentioned is that "The column CLIENT of the table wasn't found".

[jennratten · Accepted solution]

Hello @Yass91  - you are getting this error because your have a step in the transform sample file query that explicitly mentioning a column named CLIENT. It must exist in the file used as the sample file but it is not present in the new file.  This is why you get the error when the function is invoked.  If the column is not needed, you can edit the transform sample file query to eliminate the reference to the CLIENT column.  If the column is needed then you must add it to the new files.