Salesforce report Dataformat.Error Power Query

[Anonymous]

Hi all,

Trying to connect to a Salesforce report with DateTime values, however I get an error message before the Power Query window as shown below:

I've looked through the DateTime column after exporting the Salesforce report to excel and all records looks okay. Can anyone help me solve this issue? 

Best,

NicoM96

[Anonymous]

Hi @Daryl-Lynch-Bzy ,

I get the error message right after the source step. Have you seen this issue before?

[Daryl-Lynch-Bzy]

Hi @Anonymous - yes I have, but only in the context when using Table.TransformColumnTypes to change a datetime string to date.  Power Query can sometimes get confused, so you need to convert the String to DateTime then to Date.  The following shows how the error occurs when changing the DateTimeZone string straight to Date.  Instead you need to Convert to DateTimeZone first, then convert to date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1NNA1NAwxtLAyMACiKKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    // This contains your Error message
    #"Change Data Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Add DateTimeZone From" = Table.AddColumn(Source, "DateTimeZoneFrom", each DateTimeZone.FromText([Column1])),
    #"Parsed Date" = Table.TransformColumns(#"Add DateTimeZone From",{{"Column1", each Date.From(DateTimeZone.From(_)), type date}})
in
    #"Parsed Date"

Unfortunately, In this situtation, it looks like the Saleforce Connector contains a step to take the current DateTimeZone when you run the report.  It is getting confused by the format of the DateTimeZone so it cannot parse the date. 

You may need to create a support ticket with Microsoft to identify root cause because it is inside the Salesforce Connector.  

I would try the following options in the meantime:

1. Use Power BI Dataflow to see if the issue occurs online
2. change the locale setting for Power BI file

Are you using the Salesforce.Data or Salesforce.Reports?

[Rococo]

Until today the Salesforsce connector takes the current time zone when running the report. 

The data on Power Bi Desktop is refreshing in the correct format dd/mm/yyyy. However when it run on the service the dates are coming as mm/dd/yyyy. My fix is to set the other way around on the desktop so it shows correctly on the service. 

[Daryl-Lynch-Bzy]

Hi @Anonymous  - This message normally happens when Power Query can determine the Date format when trying to change the data type.  Could you please share your M code from the Advanced Editor?  I think you have to insert a step to address the issue.  If possible, insert a screenshot of the date column before any change Data Type step.