Refresh Failure in Power bi Service from Sharepoint folder but working via Desktop

The fact that the failing table varies across refresh attempts suggests a transient issue with one or more files being unreadable or structurally inconsistent. This often happens with dynamically added files—particularly if their structure slightly deviates or is saved in a non-standard format.

Files dropped into SharePoint via Power Automate may not be finalized (i.e., not fully committed or in a locked state) at the time Power BI tries to read them. Also, encoding or file integrity issues can arise if the file is passed as a Base64 blob rather than natively copied.

"Combine Files" is what I would look at first.  The default Combine Files transformation relies on the schema of a designated sample file.  When structure varies across files—even slightly (e.g., extra columns, merged cells, header misalignment); Power BI Service may fail to parse some files.  Desktop may be more forgiving or default to skipping those rows; the Service is stricter.

To fix it:

• In Power Query, after using the Combine Files wizard:
  • Go to the Transform Sample File query and make it robust (e.g., remove dynamic column references, use explicit column names).
  • Add schema validation steps early in the Transform File logic, e.g., a check like Table.ColumnNames(Source) = {"Col1", "Col2", "Col3"} to filter out malformed files.
  • Optionally add a logging table of file names and whether they passed validation.

Another possible issue is if your org uses Purview or MIP labels to encrypt excel files.  Office can encrypt these, but the Power BI Service cannot.

Last possible issue I’ll throw out is the classic file locking.  Anytime you are dealing with SharePoint and Power Automate file locking is something you have to look out for.  Delays in Power Automate are your friend. 

Please mark this post as solution if it helps you. Appreciate Kudos.

Hello jdumas93 ,

When the sample file is deleted or replaced, the query steps that rely on it (usually applied steps in the transformation query) break during service refresh.

# In Power BI Desktop, it works fine because the file is still locally cached or freshly referenced.

# The error changing from table to table is a tell-tale sign of this—Power BI is attempting to read a table structure that no longer exists or has changed unexpectedly.

To fix this:
# Rebuild the combination logic with a more resilient sample file:

-- Choose a permanent sample file that stays unchanged and isn’t one of the dynamically updated ones.

-- When using “Combine Files”, choose this as your sample query reference manually in the function.

# Make the transformation logic independent of file name/content:

-- Build logic that applies to all files in a generic way—e.g., referencing only columns that are always present.