Dataflow - SharePoint Connection Problem

[Miguel_Rojo]

Hi Community,

I've been expierience a anomal behivor when a SharePoint folder connection is executed in my Dataflow.

Here the scenario:

1. Dataflow connects to a Sharepoint folder using the .Contents() method
2. Connection is stablished successfully, and further tansformations are executed
   • In the next screenshot, you can see the 4 subfolders navegation executed to arrive to destination
   • 
3. But when I "Close and Save" my tables, the dataflow execute one extra automatic step that breks the connection: it converts my "Folders column" into a "Text Data Type":

No matter how many times I delete this annoying steps, the dataflow add them again.

I've never expierenced this problems connecting to a Sharepoint folder in the past.

Do you know why this happens and what be done to solve it?

[Miguel_Rojo]

Hi @Akash_Varuna, @v-prasare 

Thanks for the approach solutions; I really appreciate them.

I treid to apply your approach:

1. Desable the "Column Types" detection in the "Project" section of the Dataflow
2. Select the option of "Never detect column types fro unstructored process" on the "Global" section 

However, when I click "Save and Close", the Dataflow continues adding the extra step that converts my column with the SharePoint folders to "Text Data Type", and when I look the "Data Load" option, I see that Power Query doesn't repesct my past unselection and selects againg the option of "Detect Column Types":

Have you expierenced a similar issue?

[Akash_Varuna]

Hi @Miguel_Rojo , Could you try to remove it manually and then saving the Dataflow and look for any Tyoe Changes in Advanced editor and if there is any delete it and save the dataflow 

[Miguel_Rojo]

Hi @Akash_Varuna,

That's the couriose thing:

1. I delete the "undesired transfomrations steps"
2. I confirm in the "Advance Query Editor" that they have been removed
3. I confrm that my project has disable the "Automatically Identified Column Types..."
4. I proceed to "Close and Save" and "Refresh" my dataflow 
5. During the refresh process, autoamtically it keeps "Converting my table folder into a Text data type" (which breaks everything)

Is there a way to indicate that "This column representa SharePoint folder"?, so that Power Query doesn't insist in convert it to "Text"?

[v-prasare · Accepted solution]

@Miguel_Rojo, Hope your doing well.

could you please let us know if issue resolved or is still blocking you? If still issue persist, I suggest raising a support ticket.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks,

Prashanth Are

MS Fabric community support

[Miguel_Rojo]

Hi @v-prasare,

Thanks for your attention on this topic.

The issue was not solved.

The Dataflow keeps executing those undesired transformation steps (converting my sharepoint folders tables in "Text" data type) no matter how many times I disable the "Automatically Identified Column Types..."

The workaround that I'm using is:

• Instead of having one query that makes reference to "different SharePoint folders" and then referencing that "Parent query" by other "Child queries"
• I create one direct query to each "Sharepoint folder" and complete the rest of transfomations in that query (without making references)

Thanks, 

Miguel

[v-prasare]

Hi @Miguel_Rojo,

Thanks for reaching out MS Fabric community support.

@Akash_Varuna, thanks for your promt response.

When you save and refresh the Dataflow, Power BI might be trying to automatically detect column types, causing it to incorrectly convert the Content column to text or if the schema of the SharePoint folder changes (e.g., new files or folders are added), Power BI might reinterpret the column types.

Did you tried below step:

go to Dataflow settings in Power BI Service: Disable "Auto-detect column types and headers for unstructured sources."  This prevents Power BI from forcing incorrect data types.

Thanks,

Prashanth Are

[Akash_Varuna]

Hi @Miguel_Rojo , You could disable the Automatic detection type and see if it solves the issue please follow these steps

• In the Power Query Editor, go to File → Options and settings → Query Options → Global → Data Load.
• Uncheck Detect data type and relationships for unstructured sources.
  Also try to keep the folder columns in its original data type
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance