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

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.