Forum Discussion
Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=Type
I managed to work this out. I first thought the null error was being thrown inside the custom function fnGetAssetVulnerabilityDetails, so I tried to sanitise nulls before concatenating the string, but that didn't work. I then thought the null error was being thrown by the function header, so I changed the function declaration from
let fnGetAssetVulnerabilityDetails = (assetId as text, pluginId as text) =>to
let fnGetAssetVulnerabilityDetails = (assetId as any, pluginId as any) =>to allow nulls to come in, but that didn't work either.
I then discovered the error was being thrown in my main procedure while calling the function. I don't understand why Power BI wasn't passing null parameters, but added a step to remove the nulls before calling the function.
#"Change Plugin ID data type" = Table.TransformColumnTypes(#"Expand vulnerabilities",{{"Plugin ID", type text}}), #"Replace null Plugin IDs" = Table.TransformColumns(#"Change Plugin ID data type", {"Plugin ID", each if _ is null then "0" else _}), #"Get vulnerability details" = Table.AddColumn(#"Replace null Plugin IDs", "VulnerabilityDetails", each fnGetAssetVulnerabilityDetails([Tenable Asset UUID], [Plugin ID])),I hope this helps someone else.
I managed to figure this out. I first thought that the null error was being thrown inside the fnGetAssetVulnerabilityDetailscustom function, so I tried to disinfect the nulls before concatenating the string, but that didn't work. So I thought the function header threw a null error, so I changed the declaration of the function
let fnGetAssetVulnerabilityDetails = (assetId as text, pluginId as text) =>a
let fnGetAssetVulnerabilityDetails = (assetId as any, pluginId as any) =>to allow nulls, but it didn't work either.
Then I discovered that the error was being generated in my main procedure when calling the function. I don't understand why Power BI wasn't passing null parameters, but added a step to remove nulls before calling the function.
#"Change Plugin ID data type" = Table.TransformColumnTypes(#"Expand vulnerabilities",{{"Plugin ID", type text}}), #"Replace null Plugin IDs" = Table.TransformColumns(#"Change Plugin ID data type", {"Plugin ID", each if _ is null then "0" else _}), #"Get vulnerability details" = Table.AddColumn(#"Replace null Plugin IDs", "VulnerabilityDetails", each fnGetAssetVulnerabilityDetails([Tenable Asset UUID], [Plugin ID])),I hope this helps someone else.
Hi RossJ, can you please share how did you manage to connect to Tenable rest api, with PowerBi in the first place ? What parameters did you use ? Thanks!
Hi Stylebender
I wrote a custom function called fnGetJsonFromTenable:
let
fnGetJsonFromTenable = (url as text, accessKey as text, secretKey as text) =>
let
KeyString = "accessKey=" & accessKey & "; secretKey=" & secretKey & ";",
Source = Web.Contents(url,
[
Headers = [#"X-ApiKeys" = KeyString]
]
),
TextString = Text.FromBinary(Source),
JsonObject = Json.Document(TextString)
in
JsonObject
in
fnGetJsonFromTenableThen called it like this:
let
#"Define API URL" = "https://cloud.tenable.com/assets",
#"Define access key" = "[redacted]",
#"Define secret key" = "[redacted]",
#"Get JSON object" = fnGetJsonFromTenable(#"Define API URL", #"Define access key", #"Define secret key"),
#"Drill down list" = #"Get JSON object"[assets],
#"Convert to table" = Table.FromList(#"Drill down list", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Convert to table"Hope this helps!
Ross
Hi Ross
Thank you very much for your answer , it really helped , it got me closer to a solution.
The issue I'm facing right now, after I run the 2nd function, I get an error: "Access to the resource is forbidden"
If I set the authentification to anything else except Anonymous , I get the following errror :
"The 'X-ApiKeys' header is only supported when connecting anonymously"
Your help on this one is much appreciated !Hi RossJ
I hope you are still active in the community and you can help me with this : I did exactly what you described in your last message but I always get a Time Out
Could it be the size of the DB? if so how can add a filter to your M code to reduce the size?
Thanks
Salut aj1973
It's been two years since I was working on this, so my memory is hazy. I was retrieving 23 160 assets from Tenable and the process was taking a long time, so if you have more assets than that, it's possible that it is timing out.
In the first instance, I suggest you change the request to retrieve a single asset using endpoint https://cloud.tenable.com/workbenches/assets/{asset_id}/info [API doc]. That will elminate network connection timeouts as the problem.
If that proof of concept works, then you have two options: increase the timeout or decrease the payload.
a) Increase the service timeout. I've had a look, and can't see where to increase timeout for webservice calls. There's some discussion here How-to-extend-Power-BI-service-timeout-limit, but that seems to be limited to database connections.
b) Decrease the payload. Tenable allows filters [API doc]. I haven't used them, but I believe you could filter by an attribute to reduce the number of records returned. For example, retrieve assets with IP address starting with 192.168.1.x, and then iterate that from 1 to 255. I haven't tried this and my PowerBI skills are limited, so I can't suggest how to do this in a loop.
Bonne chance.
Ross
