Power BI Couldn't Refresh online - But Refresh properly in Desktop (Mashup.ErrorCode = 10224)

[Power2BI]

Hello Community,

Power BI Couldn't Refresh online in the workspace - But Refresh properly in Desktop as in below error message (couldnt find the column name):

Data source error: Expression.Error: The column 'Column1' of the table wasn't found.. Column1. Microsoft.Data.Mashup.ErrorCode = 10224. Detail = Column1. . The exception was raised by the IDbCommand interface. Table: GetIssues.

Below the used code:

fnGetAllJiraIssuesWithAllFields

let
// Define the function to accept an optional nextPageToken.
fnGetAllJiraIssuesWithAllFields = (optional nextPageToken as text) as list =>
let
// Construct the query parameters record dynamically.
// We only add the nextPageToken field if it is not null.
QueryOptions = [
jql = "project=PROJECTNAME",
fields = "*all",
maxResults = "1000"
] & (if nextPageToken <> null then [nextPageToken = nextPageToken] else []),

// Call the Web.Contents function with the dynamically built query options.
Source = Web.Contents(
"https://SERVERNAME.atlassian.net",
[
RelativePath = "/rest/api/3/search/jql",
Query = QueryOptions
]
),

// Process the JSON response.
Json = Json.Document(Source),
Issues = Json[issues],

// Check if there is a next page token.
// Record.HasFields is a safe way to check for the field's existence.
NextPageToken = if Record.HasFields(Json, "nextPageToken") then Json[nextPageToken] else null,

// Perform the recursion if a next page token exists.
NextPageIssues = if NextPageToken <> null then
@fnGetAllJiraIssuesWithAllFields(NextPageToken)
else
{},

// Combine the results.
AllIssues = Issues & NextPageIssues
in
AllIssues
in
fnGetAllJiraIssuesWithAllFields

GetIssues

let
// Call the function to get all issues, which returns a list of records.
Source = fnGetAllJiraIssuesWithAllFields(),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"})

 in #"Expanded Column1"

Column1 name has been generated autmaticall by Power BI Desktop! Please help by proposing resolution to make it refresh online.

Thanks

[Shubham_rai955]

@Power2BI This error usually happens when Power BI Service attempts to expand a column ("Column1") that was automatically generated in Desktop, but the online refresh can't find it, often due to a change in the source data shape or how Power Query handles JSON from Web.Contents. To resolve:

• Check the source JSON live in Power BI Service: confirm that "Column1" exists after Table.FromList, sometimes the returned structure changes between desktop and cloud.

• Use explicit column naming in Table.FromList: set the column name (e.g. {"IssueRecord"}) when converting from list, then reference it accordingly in Table.ExpandRecordColumn.

• Preview and validate each query step in Power Query, especially after Web.Contents, to catch missing or renamed columns before publishing.

• If possible, debug with sample data from the actual service endpoint, as column structures can shift due to API pagination or schema differences on refresh.

Making column names explicit and confirming the API's response format are key for consistent refresh across Desktop and Service

[Power2BI]

Unfortunatly still didnt work! This make me crazy as all configured well and working desktop app perfectly. Last attempt to change the code and get no errors with no result :S

let
// --- Step 1: Define Jira API connection parameters ---
JiraDomain = "SERVERNAME",
JqlQuery = "project=PROJECTNAME",

// --- Step 3: Define the recursive function for pagination ---
fnGetAllJiraIssues = (optional nextPageToken as text) as list =>
let
QueryOptions = [
jql = JqlQuery,
fields = "*all",
maxResults = "100" // Use maxResults parameter in the query options
] & (if nextPageToken <> null then [nextPageToken = nextPageToken] else []),
Source = Web.Contents(
"https://" & JiraDomain & ".atlassian.net",
[
RelativePath = "/rest/api/3/search/jql?",
Query = QueryOptions,
Headers = [
Accept = "application/json"
]
]
),
Json = Json.Document(Source),
Issues = Json[issues],
IsLastPage = Json[#"isLast"],
NextPageToken = if IsLastPage = false then Json[#"nextPageToken"] else null,
NextPageIssues = if NextPageToken <> null then @fnGetAllJiraIssues(NextPageToken) else {},
AllIssues = Issues & NextPageIssues
in
AllIssues,

// --- Step 4: Get all issues using the custom function ---
AllIssuesList = fnGetAllJiraIssues(),
#"Expanded Issues" =
Table.ExpandRecordColumn(
Table.FromList(AllIssuesList, Splitter.SplitByNothing(), {"Issue"}, null, ExtraValues.Error),
"Issue",
{"id", "self", "key", "fields"},
{"id", "self", "key", "fields"}
)
in
#"Expanded Issues"

[Power2BI]

Also tried below code to change GetIssues Query, its working well in Power BI Desktop. However, no error in online / workspace refresh but no records or data retrived (

1s

Completed

😞

let
// Call the function to get all issues, which returns a list of records.
Source = fnGetAllJiraIssuesWithAllFields(),

// Use a pre-defined table type to enforce a consistent schema.
TableType = type table
[
id = text,
self = text,
key = text,
fields = record
],

// Convert the list of records to a table using the defined schema.
// The correct syntax is to directly use the variable name 'TableType'.
#"Converted to Table" = Table.FromRecords(Source, TableType)

in #"Converted to Table"

[v-sshirivolu]

Hi @Power2BI ,
Thank you for providing the update and your revised query. Since it works on Desktop but returns no records in Service, this typically indicates a credential or connectivity issue between the two environments. Before making any code changes, please follow these steps:

Navigate to "Workspace - Dataset - Settings - Data source credentials" and re-enter your Jira credentials (OAuth2 or API token). Desktop may use cached credentials, while Service requires a new authentication.

If Jira is "on-premises", ensure you have configured an "On-Premises Data Gateway" and mapped the dataset accordingly. The Service cannot access your Jira source without a gateway.

If these steps do not resolve the issue, you may proceed to:

Update the query to use a "dynamic schema" (Record.ToTable) rather than a fixed schema to prevent mismatches when Service interprets the JSON differently.

Test a basic query that returns the raw "JSON" response in Service to determine if the API is returning empty results or if there is a schema issue.

Regards,
Sreeteja

[v-sshirivolu]

Hi @Power2BI ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[Power2BI]

Unfortunatly still didnt work! This make me crazy as all configured well and working desktop app perfectly. Last attempt to change the code and get no errors with no result :S

let
// --- Step 1: Define Jira API connection parameters ---
JiraDomain = "SERVERNAME",
JqlQuery = "project=PROJECTNAME",

// --- Step 3: Define the recursive function for pagination ---
fnGetAllJiraIssues = (optional nextPageToken as text) as list =>
let
QueryOptions = [
jql = JqlQuery,
fields = "*all",
maxResults = "100" // Use maxResults parameter in the query options
] & (if nextPageToken <> null then [nextPageToken = nextPageToken] else []),
Source = Web.Contents(
"https://" & JiraDomain & ".atlassian.net",
[
RelativePath = "/rest/api/3/search/jql?",
Query = QueryOptions,
Headers = [
Accept = "application/json"
]
]
),
Json = Json.Document(Source),
Issues = Json[issues],
IsLastPage = Json[#"isLast"],
NextPageToken = if IsLastPage = false then Json[#"nextPageToken"] else null,
NextPageIssues = if NextPageToken <> null then @fnGetAllJiraIssues(NextPageToken) else {},
AllIssues = Issues & NextPageIssues
in
AllIssues,

// --- Step 4: Get all issues using the custom function ---
AllIssuesList = fnGetAllJiraIssues(),
#"Expanded Issues" =
Table.ExpandRecordColumn(
Table.FromList(AllIssuesList, Splitter.SplitByNothing(), {"Issue"}, null, ExtraValues.Error),
"Issue",
{"id", "self", "key", "fields"},
{"id", "self", "key", "fields"}
)
in
#"Expanded Issues"

[v-sshirivolu]

Hi @Power2BI ,
Thank you for sharing the updated query. Since it functions correctly in Desktop but not in Service, please consider the following troubleshooting steps:

Re-enter credentials in Service: Navigate to Workspace - Dataset - Settings - Data source credentials. Use Basic Auth with your email address and API token. Note that Desktop may use cached credentials, while Service requires new authentication. Review Gateway configuration: If Jira is on-premises, Service will need a Gateway to access it. Set up an On-Prem Gateway and link your dataset accordingly. Update the Web.Contents call: Rather than combining RelativePath and Query, construct the URL directly as follows:
Source = Web.Contents("https://" & JiraDomain & ".atlassian.net/rest/api/3/search?" & Uri.BuildQueryString(QueryOptions), [Headers = [Accept = "application/json"]])

Adjust pagination fields: The Jira /search API uses startAt, maxResults, and total instead of isLast or nextPageToken. Please update your recursive function to reflect this.
If these steps don't resolve the issue and Service still shows no data, please submit a Support ticket with Microsoft so they can review your Service environment logs and identify if the issue is with Power BI Service or Jira API limits. To create a ticket, follow this guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

 

[v-sshirivolu]

Hi @Power2BI ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @Power2BI ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions