Anonymous access via API

[darko861]

Hi Community,

This subject has previously been discussed in this forum with mixed results. Here is the previous thread:

https://community.powerbi.com/t5/Service/Anonymous-access/m-p/21014#M5916

I'm facing a similar issue, with not being able to refresh the BI reports automatically in Services.

Here is the syntax used in Power BI desktop which extracts data from the API, and this works with no problem when using "Anonymous" authentication in Data source settings:

let
Source = Json.Document(Web.Contents("https://aptic.hrm.kontek.se/Webapi/api/employees",

[

RelativePath = "/1561ebb2-de73-454c-9c28-acee00b37dde/timereports?from=2021-04-01&tom=2023-04-30",
Headers=[Authorization="Basic 5xxx:API_Key"]
])),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Date", "IsAudited", "IsTransferredToSalary", "OvertimeMarkingBefore", "OvertimeMarkingAfter", "TimeRows"}, {"Date", "IsAudited", "IsTransferredToSalary", "OvertimeMarkingBefore", "OvertimeMarkingAfter", "TimeRows"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Date", type datetime}, {"IsAudited", type logical}, {"IsTransferredToSalary", type logical}, {"OvertimeMarkingBefore", type any}, {"OvertimeMarkingAfter", type any}, {"TimeRows", type any}}),
#"Expanded TimeRows" = Table.ExpandListColumn(#"Changed Type", "TimeRows"),
#"Expanded TimeRows1" = Table.ExpandRecordColumn(#"Expanded TimeRows", "TimeRows", {"TimeCode", "UnsocialWorkingHoursTimeCode", "FromTime", "TomTime", "TimeInMinutes", "Accounts", "InternalComment", "ExternalComment", "Billed", "BillabledTime", "Billing", "OriginFrom", "OriginTom", "HasPadlock", "HasManuallyChangedAccount", "IsGenerated", "OriginalFromTime", "OriginalTomTIme", "IsComplete"}, {"TimeCode", "UnsocialWorkingHoursTimeCode", "FromTime", "TomTime", "TimeInMinutes", "Accounts", "InternalComment", "ExternalComment", "Billed", "BillabledTime", "Billing", "OriginFrom", "OriginTom", "HasPadlock", "HasManuallyChangedAccount", "IsGenerated", "OriginalFromTime", "OriginalTomTIme", "IsComplete"}),
#"Expanded TimeCode" = Table.ExpandRecordColumn(#"Expanded TimeRows1", "TimeCode", {"Id", "Name", "Code"}, {"Id", "Name", "Code"}),
#"Expanded Accounts" = Table.ExpandListColumn(#"Expanded TimeCode", "Accounts"),
#"Expanded Accounts1" = Table.ExpandRecordColumn(#"Expanded Accounts", "Accounts", {"IsFromTimeclock", "Id", "Name", "Code", "AccountDistribution"}, {"IsFromTimeclock", "Id.1", "Name.1", "Code.1", "AccountDistribution"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Accounts1",{"IsAudited", "IsTransferredToSalary", "OvertimeMarkingBefore", "OvertimeMarkingAfter", "UnsocialWorkingHoursTimeCode", "IsFromTimeclock", "AccountDistribution", "InternalComment", "ExternalComment", "Billed", "BillabledTime", "Billing", "OriginFrom", "OriginTom", "HasPadlock", "HasManuallyChangedAccount", "IsGenerated", "OriginalFromTime", "OriginalTomTIme", "IsComplete"})
in
#"Removed Columns"

But when I try to schedule refresh in Power BI service then there appear to be some credential problems:

In the Data source settings in Services, it has established a successful connection to the API with Basic Credentials:

If you try to change authentication in Services to Anonymous to match the connection in Desktop, you get an error message:

Is there a way to establish a successful connection in Services where the BI reports can be updated through an automatic schedule refresh in Power BI without any problems?

I would really appreciate it if you can give me some suggestions on how I can solve this problem, maybe the syntax above, which works great in Desktop, should be written differently?

[blopez11 · Accepted solution]

In Power BI Desktop,
-- I would change the M code, moving "/Webapi/api/employees" to the RelativePath option and remove the Headers option all together
-- I would edit the permissions on the URL and changing type from Anonymous to Basic with the appropriate credentials

Once published, in the Power BI Service,
-- In the scheduled refresh settings, I would not use a gateway (looks like you are using one), and in the data source credentials, set type to basic with the appropriate credentials

Good luck,

[darko861]

Thank you very much! I followed your steps and it worked, I really appreciate it, this is such a great community!