Forum Discussion

sovereignauto's avatar
sovereignauto
Icon for Helper III rankHelper III
5 years ago
Solved

DATES! PowerBI Dataflow and Desktop

Good Morning, 

 

i am having a bit of an issue with dates as US - UK format 

 

im pulling through a Gateway which pulls into the dataflow "fine" with the exception that dates are in US format. 

 

i have a "group by" query that works perfect like that. 

 

but as soon as i change the location in the dataflow settings to UK this query then fails with unable to parse date (trying to have a 13 month instead of day of month) 

 

so if i leave as US this works fine and refreshes no problem, however when i then bring into Desktop it then errors becuase its US format and wants UK format! 

 

how do i get these to Sync up correctly or more to the point why would my group by query not work when chnaging to UK location on service:

 

let
  Source = ClaimsHistory,
  #"Calculated date" = Table.TransformColumns(Source, {{"Date", each DateTime.Date(_), type date}}),
  #"Grouped rows" = Table.Group(#"Calculated date", {"Date", "Inits"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
  #"Grouped rows"
Need Help
Tutorial Requests

3 Replies

    • sovereignauto's avatar
      sovereignauto
      Icon for Helper III rankHelper III
      5 years ago

      HI amitchandak 

      im not changing anything in the raw data - in my group by i am changing Datetime to date only 

       

      i do have Incremental refresh on so could it be that it has already saved the history in US format so changing it causes issues?

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Dataflow incremental refresh determines dates according to the following logic: if a refresh is scheduled, incremental refresh for dataflows uses the time zone defined in the refresh policy. If no schedule for refreshing exists, incremental refresh uses the time from the computer running the refresh.

        Please change your OS date format as UK, then restart the Power BI dekstop. Reopen the report, refresh query in query editor to see if the issue is gone. See: https://community.powerbi.com/t5/Issues/Dataflow-preview-dd-mm-yyyy-becomes-mm-dd-yyyy-when-consumin...