Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am getting error for below code.
let
url="https://us11-csl-zweb01.amelia.com/api_jsonrpc.php",
Body = "{
""jsonrpc"": ""2.0"",
""method"": ""host.get"",
""params"": {
""output"": ""extend"",
""selectTags"": ""extend"",
""evaltype"": 0,
""tags"": [
{
""tag"": ""Region"",
""value"": ""AMER"",
""operator"": 1
},
{
""tag"": ""Device Type"",
""value"": ""Rika"",
""operator"": 1
}
]
},
""auth"": ""0558a9e08c8491645d8981d680478088"",
""id"": 1
}",
Source = Json.Document (
Web.Contents(
url,
[
Headers=[
#"Content-Type"="application/json"
],
Content = Text.ToBinary(Body)
]
)
),
result = Source[result],
Table = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Table1=Table.FromRecords({
[Column1 = ""]
}),
result1= if List.IsEmpty(result) then Table1 else Table,
#"Expanded Column1" = Table.ExpandTableColumn(Table1, "Column1",{"hostid", "proxy_hostid", "host", "status", "lastaccess", "ipmi_authtype", "ipmi_privilege", "ipmi_username", "ipmi_password", "maintenanceid", "maintenance_status", "maintenance_type", "maintenance_from", "name", "flags", "templateid", "description", "tls_connect", "tls_accept", "tls_issuer", "tls_subject", "proxy_address", "auto_compress", "custom_interfaces", "uuid", "tags"}, {"Column1.hostid", "Column1.proxy_hostid", "Column1.host", "Column1.status", "Column1.lastaccess", "Column1.ipmi_authtype", "Column1.ipmi_privilege", "Column1.ipmi_username", "Column1.ipmi_password", "Column1.maintenanceid", "Column1.maintenance_status", "Column1.maintenance_type", "Column1.maintenance_from", "Column1.name", "Column1.flags", "Column1.templateid", "Column1.description", "Column1.tls_connect", "Column1.tls_accept", "Column1.tls_issuer", "Column1.tls_subject", "Column1.proxy_address", "Column1.auto_compress", "Column1.custom_interfaces", "Column1.uuid", "Column1.tags"}),
#"AddColumnToTable" = Table.AddColumn( #"Expanded Column1","Last_Update_TimeStamp_UTC", each DateTimeZone.FixedUtcNow()),
#"Expanded Column1.tags" = Table.ExpandListColumn(AddColumnToTable, "Column1.tags"),
#"Expanded Column1.tags1" = Table.ExpandRecordColumn(#"Expanded Column1.tags", "Column1.tags", {"tag", "value"}, {"Column1.tags.tag", "Column1.tags.value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Column1.tags1", List.Distinct(#"Expanded Column1.tags1"[Column1.tags.tag]), "Column1.tags.tag", "Column1.tags.value"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "LocalDateTime ", each DateTimeZone.SwitchZone([Last_Update_TimeStamp_UTC],-5)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Last_Update_TimeStamp_UTC", type datetimezone}})
in
#"Changed Type"
Any help is very much appreciable.
Thanks,
Santosh
Hi @Santosh_25
First of all note that you left inside the query the auth token -> don't know if intended - if not, please rotate it.
Second, your web request does NOT return anyting so what do you want to happen in this case?
Hi,
I want to handle the empty results from above code. Whenever their is no data in any column then column should give the result like null value.
Hi @Santosh_25
From your code, "Table1" is always an empty table which doesn't have the table column to be expanded. I think you want to expand the "result1" table right?
If my assumption is correct, you can use below code for "Table1". And replace Table1 with result1 in #"Expanded Column1" step.
= Table.FromRecords({
[Column1 = #table({"hostid", "proxy_hostid", "host", "status", "lastaccess", "ipmi_authtype", "ipmi_privilege", "ipmi_username", "ipmi_password", "maintenanceid", "maintenance_status", "maintenance_type", "maintenance_from", "name", "flags", "templateid", "description", "tls_connect", "tls_accept", "tls_issuer", "tls_subject", "proxy_address", "auto_compress", "custom_interfaces", "uuid", "tags"},{List.Repeat({null}, List.Count({"hostid", "proxy_hostid", "host", "status", "lastaccess", "ipmi_authtype", "ipmi_privilege", "ipmi_username", "ipmi_password", "maintenanceid", "maintenance_status", "maintenance_type", "maintenance_from", "name", "flags", "templateid", "description", "tls_connect", "tls_accept", "tls_issuer", "tls_subject", "proxy_address", "auto_compress", "custom_interfaces", "uuid", "tags"}))})]
})
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.