Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Santosh_25
Frequent Visitor

Expression.Error: We cannot convert the value "" to type Table.

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"

 

 

 

 

 

 

 

Santosh_25_0-1679396333346.png

 Any help is very much appreciable.

 

Thanks,

Santosh

3 REPLIES 3
ams1
Super User
Super User

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? 

vjingzhang_0-1679548707888.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors