Error parsing JSON string to Datetime format (Power BI)

[elabaigt]

Hello,

I have a parsing problem with my JSON parse after launching the test Flow send to me that there is a parse problem like the photos below:

I don't know why the JSON block don't want to parse my string into a datetime with this type of format YYYY-mm-ddThh:mm:ssZ

My JSON is like this:

Please help me !

Thank you in advance.

If you need more details tell me

[yashag2255]

Hey @elabaigt 

Simply use the intialise variable action in the beginning of the flow and then set variable action later on. BTW, you will not need these. Can you share a sample data of the get request that you are sending to powerBi? I am guessing there is some small mistake that is stopping us. 

[elabaigt]

@yashag2255 Sorry for the late reply

Yes I can this is what I try to get:

{"data":[{"id":"PV_DC","base":[{"date":"2019-06-11T00:15:00Z","value":0.0,"state":"A"},

The JSON response is correct but when the flow enter into the apply to each the flow is loading until the timeout.

Why the flow is doing this ?

[yashag2255]

@elabaigt 

Can you please share a screenshot of the flow time out and the apply to each action?

[elabaigt]

@yashag2255 

For sure:

[yashag2255]

@elabaigt 

---

@elabaigt wrote:

@yashag2255 Sorry for the late reply

Yes I can this is what I try to get:

{"data":[{"id":"PV_DC","base":[{"date":"2019-06-11T00:15:00Z","value":0.0,"state":"A"},

The JSON response is correct but when the flow enter into the apply to each the flow is loading until the timeout.

Why the flow is doing this ?

---

This is incorrect JSON. The brackets/ paranthesis and arrays are left open. can you confirm the data formatting? 

BTW, the above JSON should be like this: 

{
"data":
[
{
"id":"PV_DC",
"base":
[
{
"date":"2019-06-11T00:15:00Z",
"value":0.0,
"state":"A"
}
]
}
]
}

only the it will map appropriate array/ string values

Hope this Helps!

[elabaigt]

@yashag2255 

Yes sorry ofr this misunderstand, I have the second format that you wrote it's just a little part of my JSON. My JSON is very big and I forget to put the parenthesis 

[yashag2255]

@elabaigt 

Ohh Okay! Can you share the expression being used in the "format date time"

I also want to confirm the JSON schema if it is missing something so can you post a screenshot of the Json schema?

Did you manually enter the details in powerbi action or selected those from the drop down? 

[elabaigt]

@yashag2255 

This the format datetime : 

formatDateTime(items('Apply_to_each')?['date'],'yyyy-MM-dd''T''hh:mm:ss')

For the JSON schema :

{
    "type": "object",
    "properties": {
        "data": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "base": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "date": {
                                    "type": "string"
                                },
                                "value": {
                                    "type": "number"
                                },
                                "state": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "date",
                                "value",
                                "state"
                            ]
                        }
                    }
                },
                "required": [
                    "id",
                    "base"
                ]
            }
        },
        "success": {
            "type": "boolean"
        },
        "message": {
            "type": "object",
            "properties": {
                "code": {
                    "type": "integer"
                },
                "text": {
                    "type": "string"
                }
            }
        }
    }
}

I select the parameters from the drop down

[yashag2255]

Hey @elabaigt 

There are many issues with the JSON Schema. So we have to set that right for this flow to work. And we can do that if you can share a sample data of what you want to parse. I see many unnecssary things in here that are not needed and are blocking the way for the other variables. If you could share with me some sample data (whatever is coming from your http request remove all the sensitive info and replace that with soem test values) and that way we might be able to solve this issue. 

Hope this Helps!

[elabaigt]

@yashag2255 

Ok the issues that are in the JSON schema is created by MS Flow and his sample.

What did you mean with share sample data ?

Do you need an example with my URL or just a piece of data from my JSON base ?

[yashag2255]

@elabaigt 

It so happens that the data coming in has multiple arrays that need to be parsed (some of those might not be used in the data actually or is repeated) if you can share the part of the json that you are getiing from the HTTP request, it will be easier to create appropriate schema.  

[elabaigt]

Hi @yashag2255 ,

Thanks a lot for your reply and the sorry for the late reply.

Here it is the sample that I get from the HTTP request:

{"data":[{"id":"PV_POWER","base":[{"date":"2019-06-17T00:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T00:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T00:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T01:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T01:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T01:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T01:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T02:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T02:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T02:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T02:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T03:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T03:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T03:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T03:45:00Z","value":8.0,"state":"A"},{"date":"2019-06-17T04:00:00Z","value":16.0,"state":"A"},{"date":"2019-06-17T04:15:00Z","value":23.0,"state":"A"},{"date":"2019-06-17T04:30:00Z","value":36.0,"state":"A"},{"date":"2019-06-17T04:45:00Z","value":47.0,"state":"A"},{"date":"2019-06-17T05:00:00Z","value":56.0,"state":"A"},{"date":"2019-06-17T05:15:00Z","value":66.0,"state":"A"},{"date":"2019-06-17T05:30:00Z","value":73.0,"state":"A"},{"date":"2019-06-17T05:45:00Z","value":81.0,"state":"A"},{"date":"2019-06-17T06:00:00Z","value":90.0,"state":"A"},{"date":"2019-06-17T06:15:00Z","value":95.0,"state":"A"},{"date":"2019-06-17T06:30:00Z","value":107.0,"state":"A"},{"date":"2019-06-17T06:45:00Z","value":118.0,"state":"A"},{"date":"2019-06-17T07:00:00Z","value":129.0,"state":"A"},{"date":"2019-06-17T07:15:00Z","value":133.0,"state":"A"},{"date":"2019-06-17T07:30:00Z","value":145.0,"state":"A"},{"date":"2019-06-17T07:45:00Z","value":152.0,"state":"A"},{"date":"2019-06-17T08:00:00Z","value":182.0,"state":"A"},{"date":"2019-06-17T08:15:00Z","value":240.0,"state":"A"},{"date":"2019-06-17T08:30:00Z","value":395.0,"state":"A"},{"date":"2019-06-17T08:45:00Z","value":683.0,"state":"A"},{"date":"2019-06-17T09:00:00Z","value":915.0,"state":"A"},{"date":"2019-06-17T09:15:00Z","value":1143.0,"state":"A"},{"date":"2019-06-17T09:30:00Z","value":1606.0,"state":"A"},{"date":"2019-06-17T09:45:00Z","value":990.0,"state":"A"},{"date":"2019-06-17T10:00:00Z","value":1010.0,"state":"A"},{"date":"2019-06-17T10:15:00Z","value":712.0,"state":"A"},{"date":"2019-06-17T10:30:00Z","value":1094.0,"state":"A"},{"date":"2019-06-17T10:45:00Z","value":764.0,"state":"A"},{"date":"2019-06-17T11:00:00Z","value":614.0,"state":"A"},{"date":"2019-06-17T11:15:00Z","value":612.0,"state":"A"},{"date":"2019-06-17T11:30:00Z","value":572.0,"state":"A"},{"date":"2019-06-17T11:45:00Z","value":508.0,"state":"A"},{"date":"2019-06-17T12:00:00Z","value":437.0,"state":"A"},{"date":"2019-06-17T12:15:00Z","value":368.0,"state":"A"},{"date":"2019-06-17T12:30:00Z","value":336.0,"state":"A"},{"date":"2019-06-17T12:45:00Z","value":319.0,"state":"A"},{"date":"2019-06-17T13:00:00Z","value":292.0,"state":"A"},{"date":"2019-06-17T13:15:00Z","value":295.0,"state":"A"},{"date":"2019-06-17T13:30:00Z","value":287.0,"state":"A"},{"date":"2019-06-17T13:45:00Z","value":282.0,"state":"A"},{"date":"2019-06-17T14:00:00Z","value":259.0,"state":"A"},{"date":"2019-06-17T14:15:00Z","value":257.0,"state":"A"},{"date":"2019-06-17T14:30:00Z","value":266.0,"state":"A"},{"date":"2019-06-17T14:45:00Z","value":261.0,"state":"A"},{"date":"2019-06-17T15:00:00Z","value":248.0,"state":"A"},{"date":"2019-06-17T15:15:00Z","value":258.0,"state":"A"},{"date":"2019-06-17T15:30:00Z","value":257.0,"state":"A"},{"date":"2019-06-17T15:45:00Z","value":255.0,"state":"A"},{"date":"2019-06-17T16:00:00Z","value":242.0,"state":"A"},{"date":"2019-06-17T16:15:00Z","value":253.0,"state":"A"},{"date":"2019-06-17T16:30:00Z","value":253.0,"state":"A"},{"date":"2019-06-17T16:45:00Z","value":252.0,"state":"A"},{"date":"2019-06-17T17:00:00Z","value":237.0,"state":"A"},{"date":"2019-06-17T17:15:00Z","value":252.0,"state":"A"},{"date":"2019-06-17T17:30:00Z","value":248.0,"state":"A"},{"date":"2019-06-17T17:45:00Z","value":229.0,"state":"A"},{"date":"2019-06-17T18:00:00Z","value":199.0,"state":"A"},{"date":"2019-06-17T18:15:00Z","value":162.0,"state":"A"},{"date":"2019-06-17T18:30:00Z","value":128.0,"state":"A"},{"date":"2019-06-17T18:45:00Z","value":87.0,"state":"A"},{"date":"2019-06-17T19:00:00Z","value":75.0,"state":"A"},{"date":"2019-06-17T19:15:00Z","value":59.0,"state":"A"},{"date":"2019-06-17T19:30:00Z","value":35.0,"state":"A"},{"date":"2019-06-17T19:45:00Z","value":24.0,"state":"A"},{"date":"2019-06-17T20:00:00Z","value":15.0,"state":"A"},{"date":"2019-06-17T20:15:00Z","value":9.0,"state":"A"},{"date":"2019-06-17T20:30:00Z","value":1.0,"state":"A"},{"date":"2019-06-17T20:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T21:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T21:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T21:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T21:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T22:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T22:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T22:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T22:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T23:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T23:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T23:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-17T23:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T00:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T00:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T00:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T00:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T01:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T01:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T01:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T01:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T02:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T02:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T02:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T02:45:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T03:00:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T03:15:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T03:30:00Z","value":0.0,"state":"A"},{"date":"2019-06-18T03:45:00Z","value":14.0,"state":"A"},{"date":"2019-06-18T04:00:00Z","value":20.0,"state":"A"},{"date":"2019-06-18T04:15:00Z","value":27.0,"state":"A"},{"date":"2019-06-18T04:30:00Z","value":40.0,"state":"A"},{"date":"2019-06-18T04:45:00Z","value":58.0,"state":"A"},{"date":"2019-06-18T05:00:00Z","value":111.0,"state":"A"},{"date":"2019-06-18T05:15:00Z","value":111.0,"state":"A"},{"date":"2019-06-18T05:30:00Z","value":195.0,"state":"A"},{"date":"2019-06-18T05:45:00Z","value":148.0,"state":"A"},{"date":"2019-06-18T06:00:00Z","value":182.0,"state":"A"},{"date":"2019-06-18T06:15:00Z","value":275.0,"state":"A"},{"date":"2019-06-18T06:30:00Z","value":391.0,"state":"A"},{"date":"2019-06-18T06:45:00Z","value":382.0,"state":"A"},{"date":"2019-06-18T07:00:00Z","value":389.0,"state":"A"},{"date":"2019-06-18T07:15:00Z","value":492.0,"state":"A"},{"date":"2019-06-18T07:30:00Z","value":494.0,"state":"A"},{"date":"2019-06-18T07:45:00Z","value":469.0,"state":"A"},{"date":"2019-06-18T08:00:00Z","value":536.0,"state":"A"},{"date":"2019-06-18T08:15:00Z","value":706.0,"state":"A"},{"date":"2019-06-18T08:30:00Z","value":790.0,"state":"A"},{"date":"2019-06-18T08:45:00Z","value":942.0,"state":"A"},{"date":"2019-06-18T09:00:00Z","value":773.0,"state":"A"},{"date":"2019-06-18T09:15:00Z","value":988.0,"state":"A"},{"date":"2019-06-18T09:30:00Z","value":903.0,"state":"A"},{"date":"2019-06-18T09:45:00Z","value":751.0,"state":"A"},{"date":"2019-06-18T10:00:00Z","value":627.0,"state":"A"},{"date":"2019-06-18T10:15:00Z","value":459.0,"state":"A"},{"date":"2019-06-18T10:30:00Z","value":411.0,"state":"A"},{"date":"2019-06-18T10:45:00Z","value":378.0,"state":"A"},{"date":"2019-06-18T11:00:00Z","value":341.0,"state":"A"},{"date":"2019-06-18T11:15:00Z","value":333.0,"state":"A"},{"date":"2019-06-18T11:30:00Z","value":319.0,"state":"A"},{"date":"2019-06-18T11:45:00Z","value":304.0,"state":"A"},{"date":"2019-06-18T12:00:00Z","value":282.0,"state":"A"},{"date":"2019-06-18T12:15:00Z","value":286.0,"state":"A"},{"date":"2019-06-18T12:30:00Z","value":282.0,"state":"A"},{"date":"2019-06-18T12:45:00Z","value":276.0,"state":"A"},{"date":"2019-06-18T13:00:00Z","value":259.0,"state":"A"},{"date":"2019-06-18T13:15:00Z","value":269.0,"state":"A"},{"date":"2019-06-18T13:30:00Z","value":264.0,"state":"A"},{"date":"2019-06-18T13:45:00Z","value":262.0,"state":"A"},{"date":"2019-06-18T14:00:00Z","value":245.0,"state":"A"},{"date":"2019-06-18T14:15:00Z","value":244.0,"state":"A"},{"date":"2019-06-18T14:30:00Z","value":254.0,"state":"A"},{"date":"2019-06-18T14:45:00Z","value":255.0,"state":"A"},{"date":"2019-06-18T15:00:00Z","state":"N"},{"date":"2019-06-18T15:15:00Z","state":"N"},{"date":"2019-06-18T15:30:00Z","state":"N"},{"date":"2019-06-18T15:45:00Z","state":"N"},{"date":"2019-06-18T16:00:00Z","state":"N"},{"date":"2019-06-18T16:15:00Z","state":"N"},{"date":"2019-06-18T16:30:00Z","state":"N"},{"date":"2019-06-18T16:45:00Z","state":"N"},{"date":"2019-06-18T17:00:00Z","state":"N"},{"date":"2019-06-18T17:15:00Z","state":"N"},{"date":"2019-06-18T17:30:00Z","state":"N"},{"date":"2019-06-18T17:45:00Z","state":"N"},{"date":"2019-06-18T18:00:00Z","state":"N"}]}],"success":true,"message":{"code":0,"text":""}}

[yashag2255]

Hi @elabaigt 

Please use this in your JSON schema and test :

{
    "type": "object",
    "properties": {
        "data": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "base": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "date": {
                                    "type": "string"
                                },
                                "value": {
                                    "type": "integer"
                                },
                                "state": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Hope this Helps!

[elabaigt]

@yashag2255,

I'm sorry but for no reason my Power Bi won't work, The data stream set that I created for get the data deleted itself and I can't recreate a new data flow 

[yashag2255]

I did not get you. Can you please share more details? Do you have the data set in the powerbi workspace that you are connecting to? 

I just checked this entire thing by creating test values and test arrays and it works fine. 

[elabaigt]

@yashag2255 

I just can't create a streaming dataset with historic data analysis 

The dataset disappear a appear randomly and I've got this error message:

The dataset could not be created.
Please try again later or contact support. If you contact support, please provide these details.
Activity ID8ff481ec-84b0-4fdb-a352-b80776e8e5e0
Request IDb8f6d2e1-7277-4721-5657-8f0bdab2c52b
Correlation ID0c159887-7ea7-0e4f-d191-7728c6361d06
Status code500
TimeWed Jun 19 2019 09:34:28 GMT+0200 (heure d’été d’Europe centrale)
Version13.0.9775.203
Cluster URIhttps://wabi-north-europe-redirect.analysis.windows.net

[yashag2255]

Hi @elabaigt 

I am not sure about that one. I tested with a simple data set in my environment and it works fine. I have not tested with a particular category of the dataset as you mentioned. You might want to check this on the PowerBI Community forum too. 

[elabaigt]

@yashag2255 

Yeah I search on PBI community and add a new topic about it.

Before this bug I tried to test with your JSON and it works but I always have this timed out during the apply to each block

[yashag2255]

I see. Can you check by manually adding a value to the data set in powerbi? There must be a very small factor that we are missing here. 

You can also check the output of the json parser after the flow is run (just so that we are sure that we get the right data)

Hope this Helps!

[elabaigt]

@yashag2255 

I get what I want in the output JSON but actually I can't add data to my dataset because my dataset streaming was deleted by the problem said before