Forum Discussion
Can we skip data access (Getting data from a source ) in case we don't have access or something else
Hi KR300
Did you gpot any chance to look over below suggested code replace it in your code.
To handle errors gracefully in Power BI when a specific API call (based on a parameter like FixVersionName) fails or returns no data, and still allow your report and scheduled refresh to succeed, you can use try ... otherwise to safely fall back to a blank placeholder table. Here’s how to do it in your scenario:
********************************************************let
FixVersionName = ... // your parameter
Placeholder = #table(
{"Key", "Summary", "IssueType Name", "Component Name", "FixVersion Name", "Priority Name", "Status Name"},
{{"", "", "", "", "", "", ""}}
),SafeCall = try Json.Document(
Web.Contents(
"https://jira.tools.deloitteinnovation.us/rest/api/2/search?jql=project%20%3D%20SMAR%20AND%20issuetyp..." &
FixVersionName & "&maxResults=1000&fields=key,summary,fixVersions,priority,status,components,issuetype"
)
),Source = if SafeCall[HasError] then Placeholder else SafeCall[Value]
in
Source********************************************************
You can then continue your existing transformation steps on this Source safely, as it will always return a valid table shape (even if empty).
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth
Where does FixVersionName come from? A static list?
You cannot "skip" - you MUST return a result at the end of the Power Query code. However you CAN provide an empty table if there's nothing in Jira.
It is a Parameter that created in Power Query which has list of values like below
SFP.R.12.1
SFP.R.12.2
SFP.R.12.3
SFP.R.12.4
the Users want to see any FixVersion Release, they can change the Parameter in the PBI service , after that they click refresh the semantic model then they will that FixVersion Data.
Suppose, if anybody select SFP.R.12.3 value in the FixVersionName Parameter, the report wil be refrshed without any error by just skipping the connecion or based on any condition , hey this SFP.12.3 API doesn't work or no access or if it says any error found, pls give an Static meassage or an empty table or nothing...
but the main things is ; Schedule refresh ned to be completed successfully without any error showing in the Reftresh history
lbendlin , Below are the Transformation steps for the table
let
Source = Json.Document(Web.Contents("https://jira.tools.deloitteinnovation.us/rest/api/2/search?jql=project%20%3D%20SMAR%20AND%20issuetype%20in%20(bug%2C%20story%2C%20epic)%20and%20fixVersion%20%3D%20" & FixVersionName & "&maxResults=1000&fields=key,summary,fixVersions,priority,status,components,issuetype")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Removed Other Columns" = Table.SelectColumns(#"Converted to Table",{"issues"}),
#"Expanded issues" = Table.ExpandListColumn(#"Removed Other Columns", "issues"),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded issues", {{"issues", null}}),
#"Expanded issues1" = Table.ExpandRecordColumn(#"Replaced Errors", "issues", {"key", "fields"}, {"key", "fields"}),
// Replaced Errors in Key Column
#"Replaced Errors1" = Table.ReplaceErrorValues(#"Expanded issues1", {{"key", null}}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Replaced Errors1", "fields", {"summary", "issuetype", "components", "fixVersions", "priority", "status"}, {"summary", "issuetype", "components", "fixVersions", "priority", "status"}),
// Replaced Errors in Custome field Column
#"Replaced Errors2" = Table.ReplaceErrorValues(#"Expanded fields", {{"summary", null}, {"issuetype", null}, {"components", null}, {"fixVersions", null}, {"priority", null}, {"status", null}}),
#"Expanded priority" = Table.ExpandRecordColumn(#"Replaced Errors2", "priority", {"name"}, {"name"}),
// Replaced Errors in priority Column
#"Replaced Errors3" = Table.ReplaceErrorValues(#"Expanded priority", {{"name", null}}),
#"Expanded issuetype" = Table.ExpandRecordColumn(#"Replaced Errors3", "issuetype", {"name"}, {"name.1"}),
// Replaced Errors in issue type Column
#"Replaced Errors4" = Table.ReplaceErrorValues(#"Expanded issuetype", {{"name.1", null}}),
#"Expanded status" = Table.ExpandRecordColumn(#"Replaced Errors4", "status", {"name"}, {"name.2"}),
// Replaced Errors in status Column
#"Replaced Errors5" = Table.ReplaceErrorValues(#"Expanded status", {{"name.2", null}}),
#"Expanded fixVersions" = Table.ExpandListColumn(#"Replaced Errors5", "fixVersions"),
#"Replaced Errors6" = Table.ReplaceErrorValues(#"Expanded fixVersions", {{"fixVersions", null}}),
#"Expanded fixVersions1" = Table.ExpandRecordColumn(#"Replaced Errors6", "fixVersions", {"name"}, {"name.3"}),
#"Replaced Errors7" = Table.ReplaceErrorValues(#"Expanded fixVersions1", {{"name.3", null}}),
#"Expanded components" = Table.ExpandListColumn(#"Replaced Errors7", "components"),
#"Replaced Errors8" = Table.ReplaceErrorValues(#"Expanded components", {{"components", null}}),
#"Expanded components1" = Table.ExpandRecordColumn(#"Replaced Errors8", "components", {"name"}, {"name.4"}),
#"Replaced Errors9" = Table.ReplaceErrorValues(#"Expanded components1", {{"name.4", null}}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Errors9",{{"key", "Key"}, {"summary", "Summary"}, {"name.1", "IssueType Name"}, {"name.4", "Component Name"}, {"name.3", "FixVersion Name"}, {"name", "Priority Name"}, {"name.2", "Status Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Key", type text}, {"Summary", type text}, {"IssueType Name", type text}, {"Component Name", type text}, {"FixVersion Name", type text}, {"Priority Name", type text}, {"Status Name", type text}})
in
#"Changed Type"Can you pls help me out here.
Use try ... otherwise ... to return an empty table if the version is missing.
