Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Hi @glynwilks
In the following code, you need to make sure that your source will return a list, if the source returns a table, when you use the Table.FromList function, it will display the error:cannot convert a value of type Table to type List.
Source = Json.Document(Web.Contents(quotesUrl & Text.From(page), [Headers=[Accept="application/json", apikey=apikey]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your help, I have replaced with your code and I am still getting the same error.
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
Hi @glynwilks
I didn't offer the code, the code is in your original code, I extract it out to explain that you need to check that if your source returns a list, if it not return a list, when you he Table.FromList function, it will display the error, If the source returns a table, you can remove your step 'Convert to table'
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
69 | |
24 | |
18 | |
13 |