0
votes

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.

Your #"Expanded Column1" says to expand "Column1" but it doesn't look like there is any such column already defined.Alexis Olson
#"Expanded Column1" variable gets the data from this line #"Expanded Column1" = Table.ExpandRecordColumn(Table,"Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"}), Is anything missed ?techmaster
Right. This only makes sense if Table already has a column named Column1 but I don't think this exists in the JSON you're getting.Alexis Olson
You are right I can't find the Column1 in my JSON response. What is the best way to handle this in above code?techmaster
What are you actually trying to expand? I recommend using the names of the objects you're actually getting from the API.Alexis Olson