Power BI Service Refresh Stopped Working (Desktop Still Works)

[Jacob_Barrow]

I know there are several similar posts on here, but none of them seem to address my issue. A few days ago, my Power BI Service stopped successfully refreshing, while my Power BI Desktop still refreshes without a hitch.

The error I get is about corrupted data:

The Service will refresh if I only use files up to a certain date, which leads me to believe there is something amiss with my files.

I am importing many files from Azure Blob Storage into my Power BI Desktop, and using the "Combine Files" to combine them into one table.

To try and simplify and debug this, I filter the data down to a single file in the main query and use the same file as the Sample File when doing "Combine Files". I also do this filtering before Expanding.

A few steps that I've tried (per other forums):

1) Deleting the Power BI Desktop and reinstalling it (and clearing the Power BI Desktop cache).
2) Creating a new Semantic Model with the above data.
3) Confirming that credentials work in Service and Desktop (using files before 20231214 work)
4) Deleting all columns of data except one, which was a simple index column, to see if I could pare it down to bad data. 
5) Thinking about dates... In the Transform Sample File, I've deleted all columns with dates to rule out issues with null dates.

I think I've narrowed the fault down to the "Expanded Table Column1" step:

If I delete that step, everything works. It leads me to think it's something within my Transform File Helper Query, so here is a snapshot of each of the steps within that query:

Sample File:

Source: 

Filtered Rows:

Filtered Rows1:

Navigation1:

Transform Sample File:

Source:

Navigation:

Promoted Headers:

A major difference that I'm seeing between the working and non-working files:
- There is an additional column in the non-working files. Not sure why this would matter, though, if I'm using that same file as the Sample File.

I don't get any errors on the Desktop side, so I'm wondering if there's some Service magic that's happening here to cause the disagreement. I've been trying to figure this out for over a week, so any help y'all can provide would be tremendously appreciated.

[Jacob_Barrow]

I've also noticed that there is a column name change between the working and non-working files. I've heard it mentioned before that this can be an issue. But would it be an issue if I filtered down to only files with that matching column name changed before applying the Combine Files?

[aj1973]

Hi @Jacob_Barrow 

Did you check the Query folding of each Applied Steps?

[Jacob_Barrow]

**** I should mention that I've successfully done a refresh on the same file by uploading it to the Service through storage in Microsoft Teams. I didn't do Combine Files and just uploaded the single file, and was able to refresh in the Service. Makes me think it's related to using it as a sample file.


Other points, now that I'm thinking about it:
- If I try to use a "non-working file" (i.e., anything 12/14 or after) as the Sample File and filter the data to the "working files" (anything before 12/14) it will fail, however, if I use a 12/13 sample file for data on or before 12/13, it succeeds.
- If I try to use a pre-12/14 Sample File for any files later than 12/14, it will fail because of the added column at 12/14.

[aj1973]

What do you mean by Working and not Working File?

In Blob Storage you have Excel files like this 

with dates 20231213***, 20231211*** and you are trying to combine them in Power Query ? is it not?

if so these files, do they have the same structure?

[Jacob_Barrow · Accepted solution]

Thanks for your quick response! I finally realized that my original data source was creating two columns called "task", which was causing the corruption. Thanks again!