@synaptical Hey,
Try below steps too. I believe you are very close solve this issue.
1) Where to inspect the “JSON”/raw timezone in Power Query
- With the standard “SharePoint Online List” connector you won’t see literal JSON like ...T23:00:00Z inside the Source/Navigation step; Power Query materializes values into types (date/datetime/datetimezone).
- Quick way to check the actual timezone:
- In Power Query Editor, Add Column > Custom Column
- Name: CheckZone
- Formula:
- If your column is Start:
- try DateTimeZone.ToText([Start]) otherwise try DateTime.ToText([Start]) otherwise [Start]
- This will show whether values include an offset (e.g., +00:00, +01:00). If you consistently see +00:00 or values that resolve to UTC midnight, you likely have a UTC-write issue from Flow.
Tip: If you absolutely need to see literal JSON from SharePoint, you’d use Web.Contents against the REST API (advanced), but for 99% of BI scenarios, the text conversion above is enough to reveal the timezone/offset being carried.
2) Why your colleague sees the “right” date
- Cast to Date immediately (dropping time and timezone) OR
- Use Date.From on a UTC-normalized value OR
- Use a different connector/step ordering that normalizes UTC before dropping the time.
- The key is to normalize first, then drop the time, and to do it early in the query (right after Navigation).
3) Plug‑and‑play fixes (add one of these right after “Navigation”)
Pick the one that matches your situation. Replace "Start" with your actual column name. If you have multiple date columns, apply the transform to each.
A) If Flow wrote Date-only as UTC midnight (classic cause of “previous day” in local time)
- Normalize to UTC, then drop time:
- #"Fix dates (UTC)" = Table.TransformColumns(#"Navigation", {{"Start", each Date.From(DateTimeZone.SwitchZone(_, 0)), type date}})
Result: A value like 2026-01-16T00:00:00Z will become date 2026-01-16, regardless of your local offset.
B) If values are true local datetimes and you just want the local date
- Convert to local time, then drop time:
- #"Fix dates (Local)" = Table.TransformColumns(#"Navigation", {{"Start", each Date.From(DateTimeZone.ToLocal(_)), type date}})
C) If the column type varies (datetimezone/datetime/text) — robust one-liner
- Handles mixed types and casts to date:
- let
FixDate = (v as any) as date =>
if Value.Is(v, type datetimezone) then Date.From(DateTimeZone.ToLocal(v))
else if Value.Is(v, type datetime) then Date.From(v)
else if Value.Is(v, type date) then v
else if Value.Is(v, type text) then Date.From(DateTime.FromText(v))
else try Date.From(v) otherwise null,
#"Fixed Start" = Table.TransformColumns(#"Navigation", {{"Start", FixDate, type date}})
in
#"Fixed Start"
Swap DateTimeZone.ToLocal(v) for Date.From(DateTimeZone.SwitchZone(v, 0)) if you confirmed a UTC-write scenario and need to keep the intended date.
4) Exactly where to put the step
- Home > Transform Data > Power Query Editor
- View > Advanced Editor
- Find the line with #"Navigation" = ... (or the last step that yields your table)
- Directly after that, add one of the transforms above
- If you already have a #"Changed Type" step, insert the fix before Changed Type, or update Changed Type to set type date for your column
Example full snippet (edit names as needed):
Source = SharePoint.Tables("https://yourtenant.sharepoint.com/sites/yoursite", [Implementation="2.0"]),
#"Navigation" = Source{[Name="YourListName"]}[Items],
// Fix: normalize to UTC then cast to date
#"Fix dates (UTC)" = Table.TransformColumns(#"Navigation", {{"Start", each Date.From(DateTimeZone.SwitchZone(_, 0)), type date}})
in
#"Fix dates (UTC)"
5) Quick validation
- After the fix, show Start as type date and compare a few rows to what SharePoint shows for “Date only”.
- If you still see an off-by-one day, switch between the UTC and Local versions and recheck.
6) Common pitfalls checklist
- Don’t convert to Date after time-zone conversion that shifts across midnight unless you intend that shift.
- Apply the fix early (right after Navigation) so merges/joins and type changes operate on correct dates.
- Ensure you are using the “SharePoint Online List” connector (not “SharePoint Folder” or a custom Web.Contents) to match your colleague’s setup.
Thanks
Haish K
If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.
