Power BI - The column 'Column1' of the table wasn't found error

Question

I am getting below error when I am trying to apply the code and load the query.

The column 'Column1' of the table wasn't found

Here is the sample code of query:

    let 
    BaseUrl = "https://jira.tools.com/rest/api/2/search?jql=project in ('ABC') AND issuetype = 'Test Case'",

    JiraIDPerPage = 1000,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetJiraIDCount = () =>
        let Url   = BaseUrl & "&maxResults=0",
            Json  = GetJson(Url),
            Count = Json[#"total"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            Value = Json[#"issues"]
        in  Value,
 
    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
    PageCount   = Number.RoundUp(JiraIDCount / JiraIDPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    JiraID    = List.Union(Pages),
    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table,"Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})
in
    #"Expanded Column1"

I can't see any exceptions getting generated, However I try to apply the changes and when power BI loads the data it gives above error.

Answer 1

I was able to fix the above issue with below code.

    let 
Source = Json.Document(
            Web.Contents(JIRA_URL,
            [
                RelativePath="rest/api/2/search",
                Query=
                [
                  maxResults="100",
                  jql= EPICS_QUERY,
                  startAt="0",
                  apikey="MjY0MzgyODgyNDg4On5RnBcfBqhio"
                ]
            ]
)),

numIssues = Source[total],

startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),
 
data        = List.Transform(startAtList, each Json.Document(Web.Contents(JIRA_URL,
            [
            RelativePath="rest/api/2/search",
            Query=
                [
                  maxResults="100",
                 jql=EPICS_QUERY,
                 startAt=Text.From(_),
                  apikey="MjY0MzgyODgyNDg4On5RnBcfBqhio"
                ]
            ]))),

 iLL = List.Generate(
     () => [i=-1, iL={} ],
     each [i] < List.Count(data),
     each [
         i = [i]+1,
         iL = data{i}[issues]
     ],
     each [iL]
 ),
 // and finally, collapse that list of lists into just a single list (of issues)
 issues = List.Combine(iLL), 
 #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"issue", "fields"}),
 #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"assignee", "created", "creator", "description", "issuetype", "parent", "priority", "project", "reporter", "resolution", "resolutiondate", "status", "summary", "updated"}, {"assigneeF", "created", "creatorF", "description", "issuetypeF", "parentF", "priorityF", "projectF", "reporterF", "resolutionF", "resolutiondate", "statusF", "summary", "updated"}),
 #"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded fields", "assigneeF", {"key"}, {"assignee"}),
 #"Expanded creator" = Table.ExpandRecordColumn(#"Expanded assignee", "creatorF", {"key"}, {"creator"}),
 #"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded creator", "issuetypeF", {"name"}, {"issuetype"}),
 #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded issuetype", "priorityF", {"name"}, {"priority"}),
 #"Expanded project" = Table.ExpandRecordColumn(#"Expanded priority", "projectF", {"key"}, {"project"}),
 #"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded project", "reporterF", {"key"}, {"reporter"}),
 #"Expanded resolution" = Table.ExpandRecordColumn(#"Expanded reporter", "resolutionF", {"name"}, {"resolution"}),
 #"Expanded status" = Table.ExpandRecordColumn(#"Expanded resolution", "statusF", {"name"}, {"status"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"created", type datetimezone}, {"resolutiondate", type datetimezone}, {"updated", type datetimezone}}),
 #"Expanded parentF" = Table.ExpandRecordColumn(#"Changed Type", "parentF", {"key"}, {"parent"})
in
 #"Expanded parentF"