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 cod...
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

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

      • Negi1984's avatar
        Negi1984
        Regular Visitor
        7 years ago

        Hi,

         

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