Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |