Can't set folder in OneDrive for Business as data source

[K_S_C]

I've used the "SharePoint folder" connector to set a certain folder in my OneDrive for business as the data source. The issue I'm facing is that I reach at the point where I see the list of files in that folder, but as binary. From there I don't know what to do so that I'll keep them as separate files and not combine them into one. Any help?

[K_S_C]

@v-kpoloju-msftSpot on 😉.

As it turns out @AmiraBedh, by right clicking on the BINARY column there is no option for adding a custom column (at least in the Power BI version I use). The “Add as New Query” indeed serves the intended purpose.

What I'd like to add is that in case you already have a report (as I did), and you want to set as your new data source the said SharePoint folder, you need to update the source of each old query with the ones you got from the abovementioned by @v-kpoloju-msft process.

[v-kpoloju-msft · Accepted solution]

Hi @K_S_C,

Thank you for reaching out to the Microsoft fabric community forum.

Thank you @AmiraBedh for your inputs on this issue. After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

To connect to OneDrive for Business using the SharePoint Folder Connector, open Power BI Desktop or Excel Power Query. Navigate to Home > Get Data > More. Search for SharePoint Folder and select it. Click Connect, enter the SharePoint Site URL (including OneDrive for Business), and click OK. Wait for the list of files to load.

To filter and display only the desired folder, open the Navigator window where you'll see a list of files. Use the ‘Folder Path’ column to filter files belonging to a specific folder. Click the dropdown in the Folder Path column, select the appropriate folder, and click OK to apply the filter. Expand the Binary Column Without Combining, at this point, Power Query will display a Binary column, where all files appear as binary data. Normally, clicking "Combine" merges the files into one dataset, but we want to load them separately.

Instead of clicking "Combine," click on the small icon in the ‘Binary’ column (settings icon) next to a file. Right-click a file and choose “Add as New Query” to treat each file separately. If the files have different formats (e.g., CSV, Excel, JSON), repeat the process for each format.

Each file will now be imported as a distinct query in Power Query. Depending on the file type:

• For Excel files: Click the binary file to expand its sheets or tables.
• For CSV files: Click "Transform" to open and process each CSV individually.
• For JSON/XML: Utilize the structured format options to extract data as needed.

After you have separated the files, click "Close & Apply" in Power BI to load them into the model. In Excel, click "Close & Load" to load them into separate sheets or tables.

For more information kindly refer to the below following link:
SharePoint and OneDrive for Business files import - Power Query | Microsoft Learn
Sync in SharePoint and OneDrive - SharePoint in Microsoft 365 | Microsoft Learn
Power Query SharePoint folder connector - Power Query | Microsoft Learn

I hope this could resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[AmiraBedh]

The default behavior is to treat all files in the folder as a single dataset and prompt you to combine them. 

Enter your OneDrive for Business SharePoint site URL (usually https://yourcompany-my.sharepoint.com/personal/yourname_domain_com).

Once you see the file list with binary columns, do not click "Combine".

Instead, click Transform Data to enter Power Query.

If needed, filter the Folder Path column to select only the specific folder in your OneDrive.

Instead of clicking "Combine", right-click on the Binary column of a specific row and choose "Add Custom Column".

You can use this formula to extract the content for each file separately :

= Excel.Workbook([Content])

After adding the custom column, click the small expand icon next to it.

Power Query SharePoint folder connector - Power Query | Microsoft Learn

https://community.powerbi.com/t5/Desktop/How-to-connect-to-a-Sharepoint-folder-shared-with-me/td-p/2...