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.
#"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 ? – techmasterTable
already has a column namedColumn1
but I don't think this exists in the JSON you're getting. – Alexis OlsonColumn1
in my JSON response. What is the best way to handle this in above code? – techmaster