Forum Discussion
Solved
Power Query doesn't match Power Bi Table view
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 s...
The blank fields have in the meantime been filed out by users with actual dates, so at the moment i don't have the issue, i'll try your solution when the issue is back.
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.