hello everyone, i have a jira connection
let
FetchData = (accessToken as text, url as text, clientid as text) =>
let
URL = url,
path = "api/export/power-bi/" & clientid,
AuthHeader = "Basic " & Binary.ToText(Text.ToBinary("My Username:" & accessToken), BinaryEncoding.Base64),
Source = Json.Document(Web.Contents(URL, [RelativePath = path, Headers=[Authorization=AuthHeader]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
#"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"name", "kind", "url"}, {"value.name", "value.kind", "value.url"}),
#"Added Custom" = Table.AddColumn(#"Expanded value1", "SessionId", each Text.AfterDelimiter([value.url], "sessionId=")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Full URL", each Text.BeforeDelimiter([#"@odata.context"], "/$metadata")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Full URL", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Full URL", "Full URL.2"}),
#"Added Custom2" = Table.AddColumn(#"Split Column by Delimiter", "URL", each Text.BeforeDelimiter([Full URL], "/api")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Path", each Text.AfterDelimiter([Full URL], ".com/")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"@odata.context", "value.kind", "value.url", "Full URL", "Full URL.2", "URL"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"value.name", "TableName"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Path", "TableName", "SessionId"})
in
#"Reordered Columns"
in
FetchData
this first function will return the url path, session id and table names for each of my clients
then i have another function
let
FetchData = (accessToken as text, url as text, Path as text, ClientID as text, TableName as text, SessionId as text) =>
let
URL = url,
path = Path & "/" & ClientID & "/" & TableName,
Q =
[
sessionId = SessionId
],
AuthHeader = "Basic " & Binary.ToText(Text.ToBinary("My Username:" & accessToken), BinaryEncoding.Base64),
Source = Json.Document(Web.Contents(URL, [RelativePath = path, Query = Q, Headers=[Authorization=AuthHeader]])),
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table"
in
FetchData
the data loads in powerbi desktop and am able to build my dashboard but as soon as i publish and try to refresh in powerbi service, i get the dreadful 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.
Data source for Query1
I used the relative path and query in the web content and as i said the data works and loads but in service, no avail. any solutions?
