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 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 !