Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
glynwilks
Helper I
Helper I

Power Query Help - Datto Quote Manager API

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

 

 

 

 

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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]

 

glynwilks_0-1711342154366.png

 

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.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.