Hi everyone,
I need some help with this power query which is not working with the below error. What I am trying to achieve is to get all the quotes in our system and store the quote id as a variable. Then, it needs to run another query to pull all quoteline items per quote id that is stored. I can get the api to work querying the quotes table alone, it is only when I am trying to store I get the error.
"Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]"
let
apikey = "<API KEY>",
pageSize = 100,
quotesUrl = "https://api.kaseyaquotemanager.com/v1/quote?quoteNumber=&pageSize=" & Text.From(pageSize) & "&page=",
quoteLineUrl = "https://api.kaseyaquotemanager.com/v1/quoteline?quoteSectionID=",
// Define a function to retrieve a single page of quotes
getQuotesPage = (page as number) =>
let
// Use the Web.Contents function to retrieve data from the API
// The second argument specifies the options for the request, including the headers
// The Accept header is set to "application/json" to indicate that we want the response in JSON format
// The apikey header is set to the value of the apikey variable defined above
Source = Json.Document(Web.Contents(quotesUrl & Text.From(page), [Headers=[Accept="application/json", apikey=apikey]])),
// Convert the JSON response into a table
// The Table.FromList function takes the JSON response as the first argument
// The second argument is a function that specifies how to split the JSON response into rows
// The third, fourth, and fifth arguments are optional and can be used to specify the column names, types, and other options
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table",
// Define a function to retrieve all the pages of quotes
getAllQuotesPages = (page as number) =>
let
// Retrieve the current page of quotes
currentPage = getQuotesPage(page),
// If the current page has fewer than pageSize results, it is the last page
// Otherwise, recursively call the getAllQuotesPages function to retrieve the next page of quotes
allPages = if Table.RowCount(currentPage) < pageSize then currentPage else currentPage & @getAllQuotesPages(page + 1)
in
allPages,
// Call the getAllQuotesPages function to retrieve all the pages of quotes
allQuotes = getAllQuotesPages(1),
// Expand the Column1 to return the id column
#"Expanded Column1" = Table.ExpandRecordColumn(allQuotes, "Column1", {"id"}, {"id"}),
// Check if the #"Expanded Column1" table contains an id column
quotesSectionIDs = if Table.HasColumns(#"Expanded Column1", {"id"}) then
// If the id column is present, extract it and store the results in the quotesSectionIDs variable
Table.Column(#"Expanded Column1", "id")
else
// If the id column is not present, handle the error appropriately
// For example, you can display an error message or return an empty list
{},
// Define a function to retrieve a single page of quote line items for a given quotesSectionID
getQuoteLinePage = (quotesSectionID as number, page as number) =>
let
// Use the Web.Contents function to retrieve data from the API
// The second argument specifies the options for the request, including the headers
// The Accept header is set to "application/json" to indicate that we want the response in JSON format
// The apikey header is set to the value of the apikey variable defined above
Source = Json.Document(Web.Contents(quoteLineUrl & Text.From(quotesSectionID) & "&pageSize=" & Text.From(pageSize) & "&page=" & Text.From(page), [Headers=[Accept="application/json", apikey=apikey]])),
// Convert the JSON response into a table
// The Table.FromList function takes the JSON response as the first argument
// The second argument is a function that specifies how to split the JSON response into rows
// The third, fourth, and fifth arguments are optional and can be used to specify the column names, types, and other options
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table",
// Define a function to retrieve all the pages of quote line items for a given quotesSectionID
getAllQuoteLinePages = (quotesSectionID as number, page as number) =>
let
// Retrieve the current page of quote line items
currentPage = getQuoteLinePage(quotesSectionID, page),
// If the current page has fewer than pageSize results, it is the last page
// Otherwise, recursively call the getAllQuoteLinePages function to retrieve the next page of quote line items
allPages = if Table.RowCount(currentPage) < pageSize then currentPage else currentPage & @getAllQuoteLinePages(quotesSectionID, page + 1)
in
allPages,
// Define a function to retrieve all the quote line items for all the quotesSectionIDs
getAllQuoteLineItems = (quotesSectionIDs as list) =>
let
// Call the getAllQuoteLinePages function for each quotesSectionID to retrieve all the quote line items
allQuoteLineItems = List.Combine(List.Transform(quotesSectionIDs, each getAllQuoteLinePages(_, 1)))
in
allQuoteLineItems,
// Call the getAllQuoteLineItems function to retrieve all the quote line items for all the quotesSectionIDs
allQuoteLineItems = getAllQuoteLineItems(quotesSectionIDs)
in
allQuoteLineItems
