Can we skip data access (Getting data from a source ) in case we don't have access or something else

[KR300]

Hi MS Team,

I am getting data from Jira API's  - Dashboard about about Project Development Stories, Bugs, ... )

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"))

Based on changing the Parameter, we will get data for that particular release ( FixVersionName -- we have diff release like SFP.R.12.3,  SFP.R.12.4,  SFP.R.12.5, ).

Here is one thing, if any release is cancelled or by passed -- or like merging data of SFP.R.12.3 into SFP.R.12.4, i am getting errors while refreshing the report in PBI desktop + schedule refresh in Service also.

My Question is:

If i get error while fetching from an API , i need to skip that error ( need to skip schedule refresh failures also in service ). Because, i need to send subscriptions saying that SFP.R.12.3 was cancelled.

Is it possible? can you pls send any document file

[v-csrikanth]

Hi @KR300 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

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

[v-csrikanth]

Hi @KR300 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth.

[KR300]

@lbendlin @v-csrikanth , i couldn't resolve the issue need more help on this.

[lbendlin]

Have you tried to supply a placebo table as suggested?

[KR300]

@lbendlin . I just created fake table with the same Column header with 1 row which contain all the blank values. Getting error, i don't  know where i am missing.. 

Could pls help?

[v-csrikanth]

Hi @KR300 
As highlighted by @lbendlin @, the proposed using "try...otherwise" appears to effectively address your requirements. Could you please confirm if your issue has been resolve.
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

Thanks and regards,
Cheri Srikanth.

[lbendlin]

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.

[KR300]

@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%20issuetyp..." & 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.

[lbendlin]

Use try ... otherwise ... to return an empty table if the version is missing.

[KR300]

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