Forum Discussion
Scheduled Refresh Failed - missing credentials - Data source credentials greyed out
I was able to resolve this.
One of my data sources was an Excel spreadsheet from my OneDrive. In "Data Source Settings", the line corresponding to that Excel file had the path to the file on my local machine. I opened the file in sharepoint, copied its URL, and replaced that line in Data Source settings by the URL. This resolved my issue.
Hi Anonymous,
If you can please provide more information to help us clarify your scenario.(e.g. data source type, connection mode, advanced operation in query tables, schedule refresh settings, refresh log)
Regards,
XIaoxin Sheng
The data source is a Kusto cluster. To get the data I clicked Advanced Editor and entered the below query.
let KustoQuery =
let Source = Json.Document(Web.Contents("https://********.kusto.windows.net:443/v1/rest/query?db=artreporting&csl=.show version",[Query=[#"csl"="let *** = '***'; let *** = '***'; TableName | project meterId = resourceId, subscriptionId = reportingId, units, usageTime | where usageTime > ago(1d) | where meterId in (***,***) | extend usageDate = strcat(datepart(""Year"", usageTime),""-"",datepart(""Month"", usageTime),""-"",datepart(""Day"",usageTime)) | summarize sum(units) by subscriptionId, usageDate | join kind=inner ( cluster('***.kusto.windows.net').database('***').CustomerSubscriptions | extend subType = iff(OfferType == ""Internal"", ""Internal"", ""External"") | project subscriptionId=tolower(tostring(SubscriptionGuid)), TPID, TPName=CustomerName ) on subscriptionId | project subscriptionId, TPName, sum_units, usageDate ",#"x-ms-app"="PowerQuery",#"properties"="{""Options"":{""servertimeout"":""00:04:00""},""Parameters"":{},""PrincipalIdentity"":null,""ClientRequestId"":null,""SecurityToken"":null,""AuthorizationScheme"":null,""RequestHostName"":null,""LocalClusterName"":null,""Application"":null,""User"":null}"], Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "DataType", "Type" },
{
{ "Double", Double.Type },
{ "Int64", Int64.Type },
{ "Int32", Int32.Type },
{ "Int16", Int16.Type },
{ "UInt64", Number.Type },
{ "UInt32", Number.Type },
{ "UInt16", Number.Type },
{ "Byte", Byte.Type },
{ "Single", Single.Type },
{ "Decimal", Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String", Text.Type },
{ "Boolean", Logical.Type },
{ "SByte", Logical.Type },
{ "Guid", Text.Type }
}),
Exception = Source[Exceptions]?{0}?,
Result = if (Exception <> null) then
error Exception
else
let
DataTable = Source[Tables]{0},
Columns = Table.FromRecords(DataTable[Columns]),
ColumnsWithType = Table.Join(Columns, {"DataType"}, TypeMap , {"DataType"}),
TableRows = Table.FromRows(DataTable[Rows], Columns[ColumnName]),
TypedTable = Table.TransformColumnTypes(TableRows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3} }))
in
TypedTable
in
Result,
#"Changed Type" = Table.TransformColumnTypes(KustoQuery,{{"usageDate", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"usageDate", Order.Ascending}}),
#"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"sum_units", "Number of Tests"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"usageDate", type date}})
in
#"Changed Type1"I'm not sure what connection mode or advanced operation in query tables is.
I can't edit schedule refresh settings because it's greyed out
I'm using an organizational account to access the Kusto cluster.
Refresh history is in the image below