Power Query doesn't match Power Bi Table view

[CESTX]

I have this weird issue where the dates in Power Query which get's it's source from SQL doesn't fully match with the dates in Power Bi Table view.

As an example, i have filtered and sorted on both screenshots for the specific number and the following dates comes up, the issue is the date highligted blue on both screenshots which doesn't match. 

As you can see the requested dates are fine while the promised isn't. Both columns are not calculations and just direclty from SQL.

Any idea?

Power Query=

Power Bi=

[v-pnaroju-msft]

Thankyou, @jaineshp, @MasonMA, for your response.

Hi CESTX,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, the mismatch observed between Power Query and the Power BI report view might be due to:

1. Absence of a unique row identifier, which leads Power BI to assign default values such as 30-Dec-1899 from the SQL default date 0001-01-01 to incorrect rows during model loading.

2. Relationships or filters present in the data model may introduce row duplication or alter the row context in visuals.

Kindly follow the steps outlined below, which may help to resolve this issue:

1. Add a unique row identifier using the following expression:

Table.AddIndexColumn(Source, "RowID", 1, 1, Int64.Type)

2. Replace default dates with null values to avoid misleading placeholder values from SQL, as shown below:

Table.ReplaceValue(#"Previous Step", #date(1,1,1), null, Replacer.ReplaceValue, {"Promised Delivery Date"})

3. Sort the table by the Index column before loading using:

Table.Sort(#"Previous Step", {{"RowID", Order.Ascending}})

4. Verify the data model relationships and temporarily remove or disable them to check if the visuals realign correctly. Incorrect cardinality such as one-to-many relationships can distort rows.

5. Use simple table visuals for validation purposes. Remove any filters and aggregations, and set columns to Don’t Summarize to avoid transformations at the visual level.

We hope that the above information will assist you in resolving the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[MasonMA]

Hello @CESTX 

My speculation is there might be mixed Data types in 'Promised Delicery Data' column in Power Query. 

When the data is loaded into Power BI’s model, the column must be entirely of one date type, If even one row is text or improperly typed, Power Bi attempts to coerce it and convert text to date. 

You may use AddColumn to run a quick check by 

Table.AddColumn(Source, "DataTypeCheck", each Value.Type([Promised Delivery Date]))

Hope this helps:) 

[CESTX]

Hi @MasonMA 

Thanks for the suggestion, however they all come out with the type date, so nothing seems wrong.

[MasonMA]

@CESTX 

Would it be possible to see your sample data? Thanks 

[jaineshp]

Hey @CESTX,

I had faced the similar issue a while ago.

Here's what fixed it.

Looking at your screenshots, this appears to be a common Power BI date formatting/display issue. Here are the key steps to resolve this:

1. Check Data Types in Power Query

• Go to Power Query Editor
• Verify both date columns are set to "Date" data type (not Text or DateTime)
• If they're Text, convert them using Date.FromText() function

2. Verify SQL Source Data

• Check if the SQL source has any NULL values or invalid dates
• Look for dates like '1899-12-30' which often indicate data quality issues
• Consider filtering out these problematic dates at source

3. Refresh and Clear Cache

• Close Power BI completely
• Reopen and refresh the dataset
• Sometimes cached data causes display inconsistencies

4. Column Settings Check

• In Power Query, right-click the Promised Delivery Date column
• Check "Column quality" and "Column distribution" for any errors
• Look for data type mismatches or parsing errors

5. Model View Verification

• Go to Model view in Power BI
• Check the data type of both columns in the Fields pane
• Ensure they're both marked as Date, not Text

Please note: - 

The blue highlighted date (30-december-1899) is likely a default date from SQL when there's a NULL or conversion error. Focus on cleaning the source data and ensuring proper data types throughout the pipeline.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

[CESTX]

Hi @jaineshp 

Thanks for suggestions, sadly didn't help for my case.

In Power Query and model view, both columns was set to date.

For the SQL it is correct there is some null/blank value but this is as should be as the date for that specific column is not mandatory and a refresh didn't help.

Error check showed majority as blank and some valid but none as error.

It's weird because in Power Query both Requested date 08-08-2025 has promised date on 18-07-2025 while in model view only one of them has 18-07-2025.