cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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 ?

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

@elabaigt 

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

elabaigt
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@yashag2255 

For sure:

timedout.PNGapply to each.PNG

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

@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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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 

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

@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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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
Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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 ?

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

@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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

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":""}}

 

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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 

 

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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
Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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

 

 

Dual Super User
Dual Super User

Re: Error parsing JSON string to Datetime format (Power BI)

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
Level: Powered On

Re: Error parsing JSON string to Datetime format (Power BI)

@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

 

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Kudoed Authors
Users online (9,422)