Hi
I've a problem refreshing my datasource with my published report & could use some help.
I need to import a CSV file that I can access via an API call. To access this CSV via API, I need to know the ID of the file. To get this ID, I need to first run another API call to find out the latest ID number.
To get the ID, I have the query ReportList. It gives back 1 ID number
let
Source = Xml.Tables(Web.Contents("https://qualysapi.qualys.eu/api/2.0/fo/report/?action=list", [Headers=[Authorization="Basic xxxxxxxxx", #"X-Requested-With"="d"]])),
Table = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table,{{"DATETIME", type datetime}}),
REPORT_LIST = #"Changed Type"{0}[REPORT_LIST],
REPORT = REPORT_LIST{0}[REPORT],
#"Changed Type1" = Table.TransformColumnTypes(REPORT,{{"ID", Int64.Type}, {"TITLE", type text}, {"TYPE", type text}, {"USER_LOGIN", type text}, {"LAUNCH_DATETIME", type datetime}, {"OUTPUT_FORMAT", type text}, {"SIZE", type text}, {"EXPIRATION_DATETIME", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"ID", Order.Descending}}),
#"Calculated Maximum" = List.Max(#"Sorted Rows"[ID])
in
#"Calculated Maximum"
In another query, QualysScanResult, I import the CSV file with the ID number found in the query ReportList
let
Source =
Web.Contents(
"https://qualysapi.qualys.eu",
[RelativePath = "/api/2.0/fo/report/",
Headers = [
Authorization="Basic xxxxxxxxx",
#"X-Requested-With"="d"
],
Query = [
action="fetch",
id=Number.ToText(ReportList)
]
]
),
#"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=37, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}})
in
#"Changed Type"
I use the RelativePath function & use the paramater id=Number.ToText(ReportList) in the Query section to pass on the ID found in the 1st query.
This setup works fine when refreshing in PowerBI desktop, but when publishing the report I get following error
[Unable to combine data] Section1/QualysScanResult/Remove rows without server name references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: QualysScanResult.
