Cannot figure out why new file in folder is not loading

[ChrisAZ]

My PBIX:

https://drive.google.com/file/d/1IH5dXc52ndj-7Gs4BH_tkZ4EoOb6Q1AZ/view?usp=drive_link

I have a Source that is a folder to fuel data files that are in the same format and get refreshed into the model each month. For some reason the April file is giving an error where it seems to be indicating there is an invalid row but I cannot find anything different about the files. I have attached both March and April files for comparison.
https://drive.google.com/file/d/1FpM0VgwuiqIeOtnbvCct_8cmx9sCVS9o/view?usp=sharing

https://drive.google.com/file/d/1BqYo8IXzrEIXLshpwn9O3WZcMbVrJe_7/view?usp=drive_link

Any ideas? I am stumped.

[v-kpoloju-msft]

Hi @ChrisAZ,
Thank you for reaching out to the Microsoft fabric community forum. 

Thank you for providing your Power BI .pbix file along with the March and April data files. I have thoroughly reviewed your report and data source configuration to determine the cause of the refresh issue you are encountering.

From my analysis, I found that your Power BI report utilizes the "Folder" connector as the data source. This connector is meant to automatically ingest and combine multiple files within the folder, provided they share a consistent structure. This method is typically used for monthly data feeds, as you are doing with your fuel data files.

The issue appears to arise specifically when the April file is added to the folder. Although the structure of the file may seem identical to the March file at first glance, there is a subtle formatting difference that causes Power BI’s "Transform Sample File" query to fail. In particular:

• The April file may contain blank or malformed rows, particularly at the beginning or end of the file.
• These issues can disrupt the Promoted Headers step or cause discrepancies in column names or types during the Changed Type step in Power Query.
• As the query transformation steps are automatically generated based on the first file (usually the March file), any variation in April's file format (such as extra rows, missing headers, or additional blank lines) will trigger an error for the entire folder load.

To resolve this issue, please follow these steps: Open your .pbix file in Power BI Desktop. Go to Transform Data to access the Power Query Editor. In the Queries pane on the left, locate and select the query named "Transform Sample File" or "Sample File" – this is the template for all files in the folder.

Review the following steps: Source, Navigation, Promoted Headers, Changed Type. Before the "Promoted Headers" step, insert a step to remove top blank rows using the “Remove Top Rows” option (e.g., remove the first 1–2 rows if they are blank) or apply a filter to exclude rows where all columns are null. This ensures the correct header row is promoted. Confirm that column names and data types are consistent across all files.

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.

[ChrisAZ]

I have already tried all of this as this was the same information I got back from ChatGPT when I plugged in the data set. Thanks anyways

[v-kpoloju-msft]

Hi @ChrisAZ,

Thank you for the follow-up. I have identified few alternative workarounds that may help resolve the issue. Please follow these steps:

Inspect the Folder Query Output Directly: In Power Query, navigate to the Folder Query (not the sample transform). Click "Combine" > "Combine and Transform" again, even if it’s already set. Review the complete output of all files before transformation. Verify whether April’s file appears as a table or if it is misaligned/blank at this stage.

Extract the Error Detail Explicitly: Identify the step where the error occurs in the main query. Right-click the error cell in the table and select “View Errors”. This will display the specific row and column that failed and the reason (e.g., type mismatch, missing field, etc.).

Bypass the Sample File and Use a Custom Function Instead: The auto generated "Transform Sample File" can be unreliable. Manually create a custom function to clean and transform each file and apply it using Table.AddColumn and Table.ExpandTableColumn. This approach provides precise control and avoids issues from inconsistent headers or types.

Check for Hidden Characters in April File: Open April’s file in Notepad++ or Excel and enable Show All Characters. Search for non-printable characters, extra delimiters, or UTF encoding differences. You can also import it as Text/CSV individually in Power BI to examine the structure without the folder query logic.

Compare Schema Using Power Query Diagnostics: Utilize “View Native Query” or Power Query Diagnostics to confirm if the inferred schema (column count, names, types) of April’s file matches that of March’s file.

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.

[v-kpoloju-msft]

Hi @ChrisAZ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ChrisAZ]

So at this point, it loads the data but indicates an error in one row. I am ignoring it for now.

[v-kpoloju-msft]

Hi @ChrisAZ,
Thank you for the update. I'm pleased to hear that the data is now loading.

If the error is confined to a single row and does not affect your analysis, it is acceptable to proceed by temporarily ignoring it. However, for completeness and to ensure data integrity, I recommend the following. Investigate the Error Row:

• In Power Query, right-click the error cell and select “View Errors” to identify the specific cause (e.g., type mismatch, missing column).
• This will help determine if the issue is due to a formatting anomaly, missing data, or schema inconsistency in that particular row.

Optionally Filter Out Error Rows: To proceed without interruption and avoid issues downstream:

• Wrap the transformation logic in a Try...Otherwise statement to bypass problematic rows.
• Alternatively, filter out erroneous rows using Table.RemoveRowsWithErrors.
  Example:  = Table.RemoveRowsWithErrors(Source)​

Consider Logging the Error for Review: To keep a record of failed rows:

• Use Table.AddColumn with a try...otherwise expression to capture both successful and failed transformations simultaneously.

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.

[ChrisAZ]

It indicates a row, but I am not sure how to do those things you mentioned, and cannot identify anything about that row that is an issue.

[v-kpoloju-msft · Accepted solution]

Hi

Thanks for the follow up question. If there is an error with only one row and it’s not affecting the result, then you can ignore the error. But if it’s affecting the result, can you please share what error message you are getting so that it would be easy to arrive at a solution.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @ChrisAZ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @ChrisAZ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @ChrisAZ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-kpoloju-msft]

Hi @ChrisAZ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[ChrisAZ]

To add to this, I just tried saving my March fuel data file (which works fine) as 04-2025 and it errors the same way. This tells me it has nothing to do with the format of the new April file or any of the data inside of it, but something else it doesn't like.