Combine Files From SharePoint Folder

[UncleLewis]

Hi all,

I am using Power Query in Excel to try to combine files in a folder on my Sharepoint.

Initially, I uploaded csv files, but I kept getting error messages in in PQ when I tried to change the Sample File to the first csv file rather than the first file returned in PQ. So on my desktop I opened each csv file and resaved as .xlsx file. I uploaded the .xlsx file to a SharePoint Folder and deleted the csv files.

After many attempts and reboots, Power Query Combine Dialog finally shows all .xlsx files - no .csv files.

I selected Transform Sample File in the Queries Pane and add all transformations on my sample file.

However, once I clicked Close and Load, I just get

The Other Queries Folder has a query that is showing a warning :

The file does need a bit of cleanuip in Power Query which I did in the Transform Sample File
Original:

[MN7]

Hi there,

actually, I facing a problem to combine files from a selected folder on Sharepoint. It always worked until few days ago.

all files are same tamplate and same tab name. 

Not sure why is not working anymore, probably is about to some settings? 

Could you please support on this?

thanks in advance for any feedback

BR

M.

[rjPOINTb]

I have the same Issue! Even though my files are all the correct type, it I get the same error message each time. Would also love a solution on this. 

[edhans]

Confirm the files are not corrupt. If they are not, then I would raise a support ticket as something else is doing on. You can do so at the bottom of this page.
Support | Microsoft Power BI

If you have more specific questions, please post to a new thread as this one is over a year old.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[UncleLewis]

Thanks edhans,

I deleleted the .xlsx files from the SharePoint Folder and copied up the 3 csv files.
I stared a new consolidation workbook and as usual I received the Timesheet.xlsx file and the 3 csv files.

This time, instaed of selecting ther Combine Button, I selected the Transform Data Button

I filtered out the .xlsx file so only the 3 csv files remain.
I then combine the 3 files, so far so good.

I then started going through the transformation steps on the Transform Sample File
I see an error on Query1 in Other Queries[1] Folder when I select the 3 columns I want to keep, Right-click, and select Remove Other Columns. I clicked on Query1, I removed ChangedType from Applied Steps. The error went away.

I went back to the Transform Sample File to continue any needed transformations.

The 3 files are now merged as required.
Thanks for your good help!

-w

[edhans]

In Power BI, there is a setting to not detect data types on unstructured sources (CSV files, Excel files, etc.) I have that enabled, becaause that #"Change Type" that gets automatically added can cause issues. I prefer to change the types only on the files I keep, and I prefer to decide the type.

[UncleLewis]

Thanks edhans,

It appears that is the default setting in Power BI.
In all items above, I'm using Power Query in Excel.

Thanks

-w

[edhans]

It is also in Excel's Power Query, but only if you are on Office 365. It was added in mid-late 2020, so it isn't in Office 2016 or 2019. Maybe Office 2022. But Office 365 today.

[UncleLewis]

Thanks edhans,

The first file it is picking is a timesheet file that I do not need - just the 3 csv files in MyDemo Folder.

I tried entering additional URL info when prompted for the SharePoint Folder but I received an error that I could only select the root of the SharePoint Folder.

Thanks,

-w

[edhans · Accepted solution]

So before you do the Combine operation, use the filters to filter out everything you do not need.

For example, set the filter to only keep files that end in CSV. If there are stilll files there you don't want, then start filtering to keep only files with certian words in them.

Then do the combine operation. Not only will this select a valid sample file, it will keep the entire thing from breaking. Even if this had selected the file you wanted first, it would have used that file as a template to transform your Timesheet file, which would have likely broken.

One other thing you can do that I recommend. If your files are in one folder, use SharePoint.Contents().

When you create a connection to SharePoint folders it uses the SharePoint.Files() function by default. In the formula bar for the SOURCE row, change it to say SharePoint.Contents("https://blahblahblah" [apiwhtever]) - just change Files to Contents. Case sensitive.

Now you will browse by folder. Usually you click on Shared Documents table, then down to the folder you want. From there you can transform an entire folder.

This is the best way when all files are in one folder. Much faster than SharePoint.Files performs.

One caveat - if you navigate down to the wrong folder there is no way to go back up without manually editing the M code. Usually faster to just delete the NAVIGATION step and start over from Shared Documents.

[edhans]

Why did you change the sample file it picked? The files all need to be the same format - same columns, data, etc. The sample file is just the framework to get going. No need to pick the file you want. If you have 200 files, all the same format, just different data (months, weeks, divisions, whatever) it shouldn't make a difference what the sample file is. It will use 1 file schema to process 200 files.

If the files are different, you need to filter them before doing a combine operation, and then transform each group separately. You cannot have one sample file created automatically that will processes different formats. You could do it with one, but it would involve a lot of manual coding.

I would remove the XLSX files, put the CSV files back and start over. Let Power Query do its thing. Then if you don't like the results and can explain why the sample file it chose was inferior to the one you wanted it to choose, post back with the differences of those two files.