March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
Hi @Negi1984,
Why not just following the built-in functions? Please refer to the snapshot below.
Best Regards,
Dale
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
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.
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
Hi,
I replace the below code with my PQ code. but its giving me another error like below :-
Hi
Could you share a screenshot from your previous step ?
/Erik
Hi,
Please find the snapshot of previous steps :-
Hi @Negi1984,
Why not just following the built-in functions? Please refer to the snapshot below.
Best Regards,
Dale
Hi Dale,
Thanks a lot for your valuable. Its working fine now . Now no error.
Regards,
Rajender
Hi,
I am able to get the desired result. Thanks a lot for your valuable time.
Regards,
Rajender
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
164 | |
116 | |
63 | |
57 | |
50 |