cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elabaigt
Level: Powered On

Error parsing JSON string to Datetime format (Power BI)

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:

aide_1.PNGaide_2.PNGaide_3.PNG

 

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:

json.PNG

 

Please help me !

 

Thank you in advance.

 

If you need more details tell me

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

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

Hi @elabaigt 

Yes! So the JSON parser supports limited datatypes (string array and object for now) and you can further perform data operations on the results. So you have to convert the "date" parsed from JSON as a string to a date time format datatype. you can use the "formatDateTime()" function from expression editor to do this. 

 

Hope this Helps!

View solution in original post

41 REPLIES 41
Dual Super User
Dual Super User

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

Hi @elabaigt 

 

Can yo share details on how you have designed the json schema? I tried with the following and it worked fine for me. Please have a look at the screesnhot: 

fja.png

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Dual Super User
Dual Super User

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

@elabaigt 

 

You could try replacing your schema with:

{
    "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": "string"
                                },
                                "state":{
                                    "type":"string"
                                }
                            },
                            "required": [
                                "date",
                                "value", "state"
] } } }, "required": [ "id", "base" ] } } } }

Later, covert the value of the "value" from string to integer.

 

Hope this Helps!

elabaigt
Level: Powered On

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

Thank you for your reply @yashag2255 ,

 

I want to connect a REST Full API with power bi, below there is the scheme :

schema entier.PNG

And this is what is in the scheme:

Schéma.PNG

When I try to launch the connection I have a bad request with the next error : 

"message": "Error parsing request for dataset sobe_wowvirtualserver|54a2af5d-29aa-4a58-aac2-4be787d1b138: Error encountered by JSON parser when reading input data for column '<pi>Date</pi>': <pi>Could not convert string to DateTime:
 
I tried to add a date converter but nothing happen, Ialways have the same parsing error.
 
Thank you in advance !
Dual Super User
Dual Super User

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

Hi @elabaigt 

Yes! So the JSON parser supports limited datatypes (string array and object for now) and you can further perform data operations on the results. So you have to convert the "date" parsed from JSON as a string to a date time format datatype. you can use the "formatDateTime()" function from expression editor to do this. 

 

Hope this Helps!

View solution in original post

elabaigt
Level: Powered On

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

Ah by the way, I also have a problem in the power bi block. 

When I want to select a variable the block don't allow me to choose the JSON variable.

 

Below there is an example:

pbipbl.PNG

elabaigt
Level: Powered On

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

Thank you @yashag2255 ,

 

But how can I apply this "formatdatetime" ? 

I tried to applicate the formula but I don't know where

Dual Super User
Dual Super User

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

Hi @elabaigt 

 

That is because you are applying to each element of data only. You have to parse the base array also. see the screenshot below: 

yyye.pngthe expression I am using for the date formatting is: 

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

here, apply to each 2 is the name of the action where the date is being parsed from the base array. and if you set the dynamic selector, you will see the ID, Value and Date options to be selected too. 

 

I am using the Excel example, you can replicate for powerBi. 

 

Hope this Helps!

elabaigt
Level: Powered On

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

Thank you very much @yashag2255 we are almost at the end.

 

I do what you said but when I add the second apply to each the "base" ooutput don't appear.

elabaigt
Level: Powered On

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

@yashag2255 I have an other problem.

I also don't have access to the other variables of my JSON, look at the photo below:

dynamic content.PNG

 

Thank you for helping me and take time to answer to my question !

Dual Super User
Dual Super User

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

Hi @elabaigt 

 

There might be some issue in the json parse schema. Can you please checka and confirm that?

elabaigt
Level: Powered On

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

Hi @yashag2255 ,

 

I tried to copy paste into the sample the JSON format that I received from my API but when I launch the test the sample and the API said that the variables don't match.

 

You can see below a test where the JSON is ok:

analyse json.PNG

Dual Super User
Dual Super User

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

@elabaigt 

 

Okay! So you have to first get the api response and then copy that as is and then in the JSON parse action, click on add sample schema and then paste the entire thing. It will generate the schema and give you the desired output variables. can you try this and confirm?

elabaigt
Level: Powered On

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

@yashag2255 ,

 

I already try this but when I launch the test I have this error:

error jso.PNG

elabaigt
Level: Powered On

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

@yashag2255 ,

 

The scheme that I use and work is below:

Schéma.PNG

I just erased the type of the inout and that work

Dual Super User
Dual Super User

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

@elabaigt 

You will have to provide the type for each variable. I have posted the schema sample in my 2nd reply. you can check that and replicate. e.g.:

"date": {

"type": "string"

}

 

only then the values will populated and extracted to be available in the dynamic data selector. 

 

Hope thsi Helps!

elabaigt
Level: Powered On

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

@yashag2255 ,

Yes! I found the error when I integrate the sample the type of value was integer and that type isn't good I replaced it with number and now the system work.

 

But now the values that I received isn't correct.

 

For example I have only 0 for the value and on power BI Ihave some number completely incoherent 

elabaigt
Level: Powered On

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

@yashag2255 ,

 

I think I found the problem but I don't know how manage it.

 

The value that I receive don't do the differences between the LOW_POWER values and the PV_DC values how can I dissociate the values into th JSON ?

Dual Super User
Dual Super User

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

@elabaigt 

 

You will have to check what values go where. If there is any calculation to be done, you can initialise variables and process those and then reference the variables in the PowerBi action. 

 

Hope this Helps!

elabaigt
Level: Powered On

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

@yashag2255

 

How can I initialise this variables ?

 

Sorry for all this questions, I think you know, I'm a beginner to flow

 

Helpful resources

Announcements
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!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,865)