Forum Discussion

Negi1984's avatar
Negi1984
Regular Visitor
7 years ago
Solved

How to Remove All columns Error while importing Json table

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

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Negi1984,

     

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

    How_to_Remove_All_columns_Error_while_importing_Json_table

     

    Best Regards,
    Dale

10 Replies

  • donsvensen's avatar
    donsvensen
    Skilled Sharer
    7 years ago

    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's avatar
      Negi1984
      Regular Visitor
      7 years ago

      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's avatar
        donsvensen
        Skilled Sharer
        7 years ago

        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