Dynamic data source refresh using Power BI Service

[RT8]

Hi Anybody, I need help. My M following M code works in desktop version. But when I try publish Power BI service I get the following error "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.". As you see in the code below I am using relative path and query. What am I missing? Any help provided is much appreciated.

let
APIName = "scans",
myList = GetbaseURLAndAPIKeyAndlimit(),
baseURL = myList{0},
APIKey = myList{1},
mylimit = myList{2},
header = [#"x-apikey"= APIKey],
//extendedURL = baseURL & APIName & mylimit,
myList1 = {APIName, mylimit},
extendedURL = Text.Combine(myList1),
Source = Json.Document
(
Web.Contents
(
baseURL,
[RelativePath = extendedURL,
Headers = header]
)
),
Source1 = Source,
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"scan_id", "scan_type", "scan_date", "scan_description", "scan_mitigated", "asset_count", "scan_file_name", "finding_count_critical", "finding_count_high", "finding_count_medium", "finding_count_low", "finding_count_informational", "finding_count_pass", "finding_count_fail", "finding_count_warning"}, {"Column1.scan_id", "Column1.scan_type", "Column1.scan_date", "Column1.scan_description", "Column1.scan_mitigated", "Column1.asset_count", "Column1.scan_file_name", "Column1.finding_count_critical", "Column1.finding_count_high", "Column1.finding_count_medium", "Column1.finding_count_low", "Column1.finding_count_informational", "Column1.finding_count_pass", "Column1.finding_count_fail", "Column1.finding_count_warning"})
in
#"Expanded Column1"

[Anonymous]

Hi @RT8 ,

Has your problem been solved and if so, please accept the correct solution. Thank you!

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RT8 · Accepted solution]

I am very sorry for the delay in responding. My problem is yet to be resolved completely. After reviewing all the posts, I understood that I cannot use a variable when calling Web.Content for the base URL. But this means I have to hard code APIKey. This solution was not acceptable to our team. Also this meant the user is anonymous which was not acceptable to power BI Service (no problems with power BI desktop). Hence we have opted to get username and password option rather than APIKey option. We are in the process of getting approvals for that option. As soon as that happens I will verify the resolution in Power BI service for data refresh and accept the solution.

[RT8]

Hi Everyone,

We could not find a workable solution in Power BI service without exposing APIKey in the code especially for base URL authentication. We took a different route (hence the delay in responding). We created a python script using the apikey to access the APIs. Using this python scripts we created a windows executable (.exe file). We run this .exe and apikey is not exposed in that and created .csv file with required data. Power BI accesses the csv file and get the same data. We run this script as frequently we need (as per Power BI data refresh frequency) using windows task scheduler. This is our current solution until we find more elegant solution.

[GilbertQ]

Hi @RT8 

Thanks for letting us know!

[GilbertQ]

Hi @RT8 

This is because in your inquiry you are still passing your URL dynamically. Here is a podcast from Chris Webb explaining how to do this. Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...