Forum Discussion

Power2BI's avatar
Power2BI
Icon for Helper I rankHelper I
1 year ago

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

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
Need Help
Tips and Tricks

9 Replies

  • Power2BI's avatar
    Power2BI
    Icon for Helper I rankHelper I
    1 year ago

    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 (

    1sCompleted

    😞

    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"

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

         

  • Shubham_rai955's avatar
    Shubham_rai955
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Power2BI
      Icon for Helper I rankHelper I
      1 year ago

      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"