Formatting Data from Sharepoint Folder

Hi Grant

This is a common issue that can occur when working with CSV files in Power Query, especially when the data is being sourced from different locations like SharePoint and local folders. The issue you’re experiencing is likely due to how Power Query interprets the CSV files from SharePoint.

When Power Query reads a CSV file from a local folder, it uses the comma as a delimiter to separate the columns, which is the standard for CSV files. However, when the same file is read from a SharePoint folder, Power Query might interpret the data differently. This could be due to the way SharePoint handles and stores data, or it could be due to the presence of additional characters or formatting in the SharePoint version of the file.

Here are a few steps you can take to resolve this issue:

Check the CSV files on SharePoint: Open the CSV files directly from the SharePoint folder and check if there are any differences in the data or formatting compared to the local version of the files. Look for any additional characters or formatting that might be causing Power Query to interpret the data differently.

Specify the delimiter in Power Query: When importing the CSV files into Power Query, you can specify the delimiter that should be used to separate the columns. This can help ensure that Power Query interprets the data correctly, regardless of where it’s being sourced from.

Use Power Query’s ‘Clean’ function: Power Query has a ‘Clean’ function that can help remove any additional characters or formatting from your data. This can be particularly useful if the issue is being caused by additional characters or formatting in the SharePoint version of the files.

Re-upload the CSV files to SharePoint: If all else fails, you might want to try re-uploading the CSV files to SharePoint. It’s possible that something went wrong during the initial upload that’s causing the issue.

Let us know if your question remains after following those steps


Kind regards

Richard

STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector