The DateTimeOffset text '2019-01-01T00:00:00' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?

[Lucian]

Hello,

I have a error when trying to get some data using Power BI Desktop from an OData connector:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Dynamics NAV: Request failed: The remote server returned an error: (500) Internal Server Error. (The DateTimeOffset text '2019-01-01T00:00:00' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range.). The current operation was cancelled because another operation in the transaction failed. '.

I have a "'function" type query, named fxBI_G_L_Entry, that should get data from an Navision table for a specific company:

(strCompanyName as text) as table =>
let
    Source = DynamicsNav.Contents(NAVServiceURL, null),
    SelectCompany = Source{[Name=strCompanyName]}[Data],
    BI_G_L_Entry_table = SelectCompany{[Name="BI_G_L_Entry",Signature="table"]}[Data],
    #"Removed Unnecessary Columns" = Table.SelectColumns(BI_G_L_Entry_table,{"Entry_No", "G_L_Account_No", "Posting_Date", "Document_Type", "Document_No", "Debit_Amount", "Credit_Amount", "Amount", "Business_Unit_Code"}),
    #"Filter 4 Incremental Refresh" = Table.SelectRows(#"Removed Unnecessary Columns", each [Posting_Date] >= DateTime.AddZone(RangeStart,2,0) and [Posting_Date] < DateTime.AddZone(RangeEnd,2,0))    
in
    #"Filter 4 Incremental Refresh"

Then, this function is used to "consolidate" the data from all the companies in the database using the following query named ALL_BI_G_L_Entry:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0}))
in
    #"Expanded fxBI_G_L_Entry"

 Invoking function using a single company will return the proper results, but when running the ALL_BI_G_L_Entry I get the mentioned error.

What could cause this problem?

Kind Regards,

Lucian

[edhans]

It might be an issue with the ODATA feed and trying to fold the query. Just for grins, wrap the step immediately before you add this formula with Table.Buffer(). The entire previous line in M code.

That will break folding and tell Power Query to process locally. If it works and performance isn't bad, great. If it works and performance is way worse, then you'll need to talk to someone really knowledable about your OData source. If it still fails with the same error, I'd be very suprised as Table.Buffer stops communications with the server, but would like to hear back.

[Lucian]

Hi @edhans ,

And thank you for your quick intervention. The ideea is to avoid as much as possible breaking the query folding, because I would like to benefit from "incremental refresh". How do I prove that incremental refresh will work is another problem I have 😥, but for now I know I should have "query folding".

I don't quite understand exactly where I sould put the Table.Buffer() function, so an "line example" would be helpful. 😁

Meanwhile, I have tried to put the date filtering line in the "consolidation" query not in the function, and so it works.

The function query became:

(strCompanyName as text) as table =>
let
    Source = DynamicsNav.Contents(NAVServiceURL, null),
    SelectCompany = Source{[Name=strCompanyName]}[Data],
    BI_G_L_Entry_table = SelectCompany{[Name="BI_G_L_Entry",Signature="table"]}[Data],
    #"Removed Unnecessary Columns" = Table.SelectColumns(BI_G_L_Entry_table,{"Entry_No", "G_L_Account_No", "Posting_Date", "Document_Type", "Document_No", "Debit_Amount", "Credit_Amount", "Amount", "Business_Unit_Code})    
in
    #"Removed Unnecessary Columns"

And the "consolidation" query became:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0})),
    #"Filter 4 Incremental Refresh" = Table.SelectRows(#"Expanded fxBI_G_L_Entry", each [Posting_Date] >= DateTime.AddZone(RangeStart,2,0) and [Posting_Date] < DateTime.AddZone(RangeEnd,2,0))
in
    #"Filter 4 Incremental Refresh"

Even if it seems to return the proper data, I think "query folding" would be broken and also the "incremental refresh".

Is there a way to "prove" or test is still in effect? Because the "Diagnose step" option will not return any information related to the date filters, only for the "selected columns".

Another question would be why the problem is related only to this table? Because I have similar situation with Exchange Rate table but there is no problem here. Of course the G_L_Entry is the "biggest table" - this could be the problem?

Kind Regards,

Lucian

[edhans]

Try changing this:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0}))
in
    #"Expanded fxBI_G_L_Entry"

to this:

let
    Source = fxBI_CompanyName(),
    Source2 = Table.Buffer(Source)
    #"Invoked Custom Function" = Table.AddColumn(Source2, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0}))
in
    #"Expanded fxBI_G_L_Entry"

When you break folding, it only breaks it going forward. If your incremental refresh happens earlier in the process, that is unaffected. Get it working first. Worry about Incremental Refresh later.

For other places you want to test Table.Buffer() you'd normally do this: (I added a line because I don't think you can do that on the Source line, but you can on all others). Below I broke it right at the Table.AddColumn step. You can put Table.Buffer generally anywhere. Now that I think about it, below is the same as me adding the line earlier. Source folds, Table.AddColumn and after no longer will. You can move the Table.Buffer up/down as needed when troubleshooting.

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.Buffer(Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name]))),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0}))
in
    #"Expanded fxBI_G_L_Entry"

. 

As for is folding working, right-click on any step and see if "View Native Query" is showing or is grayed out. If greyed out, 99% of the time that means folding has stopped working.

[Lucian]

Hello @edhans ,

And thank you very much for your time.

Unfortunately, both of your examples have lead to the same error.

It seems I have to forget about incremental refresh and date filtering in this dataset. ☹️

If you have any other ideea, I'm willing to try anything.

Kind Regards,

Lucian