How to Remove All columns Error while importing Json table

[Negi1984]

Hi All,

I want to replace all Errors messages with "NULL" in all the columns.

I am import data from JSON and it is giving me error like below for mulitple columns .

Below is the code which I am using now :-

let
Source = Json.Document(Web.Contents("XXXXXXXXXXXXXXXXX")),
data1 = Source[data],
Table = Table.FromRecords(data1)
in
Table

Can anybody suggest me , which custom code can help me to replace error with Null in one go ?

Regards,

Rajender

[donsvensen]

Hi Rajender,

The function table.FromRecords has an optional parameter where you can specify how the function should handle missing fields

https://msdn.microsoft.com/en-us/query-bi/m/table-fromrecords

So you can add MissingField.UseNull to set finishTime to null - and you should set the second argument to specify you table's columns and data type as shown in the documentation.

/Erik

[Negi1984]

Hi Donsvensen,

Thanks a lot for your prompt feedback. I check the link but unable to understand.

Could you please assist , what modification exactly I need to require in 3rd line ?

let
 Source = Json.Document(Web.Contents("XXXXXXXXXXXXXX")),
 data1 = Source[data],
 Table = Table.FromRecords(data1)

in
    Table

My Headers in Data are mentioned below :-

dataComplete

finishTime

hostName

jobExecutionId

jobExecutionNumber

jobFinishedEventId

jobInstance

jobQueuedEventId

jobStartedEventId

queuedTime

ran

resultCode

startTime

track

Thank you once again for your support.

[donsvensen]

Hi

Properly looks something like this

Table =Table.FromRecords(data1, {"dataComplete", "finishTime", "hostName", "jobExecutionId", "jobExecutionNumber", "jobFinishedEventId", "jobInstance", "jobQueuedEventId", "jobStartedEventId", "queuedTime", "ran", "resultCode", "startTime", "track"}, {"dataComplete", "finishTime", "hostName", "jobExecutionId", "jobExecutionNumber", "jobFinishedEventId", "jobInstance", "jobQueuedEventId", "jobStartedEventId", "queuedTime", "ran", "resultCode", "startTime", "track"}, MissingField.UseNull )

Hope this helps you

/Erik

[Negi1984]

Hi,

I replace the below code with my PQ code. but its giving me another error like below :-

[donsvensen]

Hi

Could you share a screenshot from your previous step ?

/Erik

[Negi1984]

Hi,

Please find the snapshot of previous steps :-

[v-jiascu-msft · Accepted solution]

Hi @Negi1984,

Why not just following the built-in functions? Please refer to the snapshot below.

Best Regards,
Dale

[Negi1984]

Hi Dale,

Thanks a lot for your valuable. Its working fine now . Now no error.

Regards,

Rajender

[donsvensen]

Hi @Negi1984

I agree with @v-jiascu-msft - if you still have problems let us know

Best 

Erik

[Negi1984]

Hi,

I am able to get the desired result. Thanks a lot for your valuable time.

Regards,

Rajender