Forum Discussion
Expression.Error: We cannot apply field access to the type Text.
I'm trying to get a list of solutions to vulnerabilities. The API link uses the vulnerability ID which is a combination of text and numbers. The error is in the GetSolution function where I'm using the text from the vulnerability ID column.
let
Vulnerabilities = () =>
let
Source1 = Json.Document(Web.Contents("https://[link]/api/")),
page = Source1[page],
count = Number.ToText (page[totalResources]),
Source2 = Json.Document(Web.Contents("https://[link]/api/?page=0&size=" & count & "&sort=id,asc")),
Resources = Source2[resources],
Table = Table.FromList(Resources, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expand = Table.ExpandRecordColumn(Table, "Column1", {"id"}, {"id"}),
txt = Table.TransformColumnTypes(Expand,{{"id", type text}})
in
txt,
GetSolution = (column) =>
let
Source = Json.Document(Web.Contents("https://[link]/api/" & Text.From(column) & "/solutions")),
Resources = Source[resources],
Resources1 = Resources{0},
Summary = Resources1[summary]
in
Summary,
VulnerabilitiesList = Vulnerabilities(),
Solutions = Table.AddColumn (VulnerabilitiesList, "Solution", each GetSolution([id]))
in
Solutions
This helped a lot, but I don't know exactly why, maybe because I was able to call the column from the table I was using. As I'm writing this I realize I could really condense the link situation, but in any case, I'm glad it's working. Thanks for your help!
let
Source1 = Json.Document(Web.Contents("https://[link]/api/")),
page = Source1[page],
count = Number.ToText (page[totalResources]),
Source2 = Json.Document(Web.Contents("https://[link]/api/?page=0&size=" & count & "&sort=id,asc")),
Resources = Source2[resources],
Table = Table.FromList(Resources, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expand = Table.ExpandRecordColumn(Table, "Column1", {"id"}, {"id"}),
txt = Table.TransformColumnTypes(Expand,{{"id", type text}}),
Solution = Table.AddColumn(txt, "Solution", each Json.Document(Web.Contents("https://[link]/api/" & [id] & "/solutions"))),
#"Expanded Solution" = Table.ExpandRecordColumn(Solution, "Solution", {"resources"}, {"Solution.resources"}),
#"Expanded Solution.resources" = Table.ExpandListColumn(#"Expanded Solution", "Solution.resources")
in
#"Expanded Solution.resources"
2 Replies
Since you are pulling a solution for each vulnerability why not integrate the second web call into the Vulnerabilities function? It would go in after the Expand line.
This helped a lot, but I don't know exactly why, maybe because I was able to call the column from the table I was using. As I'm writing this I realize I could really condense the link situation, but in any case, I'm glad it's working. Thanks for your help!
let
Source1 = Json.Document(Web.Contents("https://[link]/api/")),
page = Source1[page],
count = Number.ToText (page[totalResources]),
Source2 = Json.Document(Web.Contents("https://[link]/api/?page=0&size=" & count & "&sort=id,asc")),
Resources = Source2[resources],
Table = Table.FromList(Resources, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expand = Table.ExpandRecordColumn(Table, "Column1", {"id"}, {"id"}),
txt = Table.TransformColumnTypes(Expand,{{"id", type text}}),
Solution = Table.AddColumn(txt, "Solution", each Json.Document(Web.Contents("https://[link]/api/" & [id] & "/solutions"))),
#"Expanded Solution" = Table.ExpandRecordColumn(Solution, "Solution", {"resources"}, {"Solution.resources"}),
#"Expanded Solution.resources" = Table.ExpandListColumn(#"Expanded Solution", "Solution.resources")
in
#"Expanded Solution.resources"
