API Dataflow query returns error when publishing trying to publish to the Lakehouse
I am using dataflows gen2 to publish from an API to the Lakehouse (delta table). My first query pulls 50 records into the DF preview, then posts successfully to the Lakehouse. However, when I modify the M code to iterate over multiple API pages (pages <= 1), I get the exact table and schema in my DF preview, however when I attempt to publish what appears to be the exact same 50 records, I get an error. Please see both versions of the query attached. I've masked the API URL. Everything else is the same. Here are both versions of M code: **** This version completes the table in Dataflows, and allows me to post as Delta in my Lakehouse let Source = Json.Document(Web.Contents("http://192.000.00.18:20002/api/CUSTOMERS.PBI")), #"Converted to table" = Table.FromRecords({Source}), #"Expanded customers" = Table.ExpandListColumn(#"Converted to table", "customers"), #"Expanded customers1" = Table.ExpandRecordColumn(#"Expanded customers", "customers", {"id", "date_entered", "name", "full_name", "phone", "customer_type", "customer_type_desc", "email", "terrritory", "rank", "sales_rep", "sales_rep_name", "vendor_nbr", "credit_limit", "cat_tax", "tax_nbr", "shop_name", "billing_address"}, {"customers.id", "customers.date_entered", "customers.name", "customers.full_name", "customers.phone", "customers.customer_type", "customers.customer_type_desc", "customers.email", "customers.terrritory", "customers.rank", "customers.sales_rep", "customers.sales_rep_name", "customers.vendor_nbr", "customers.credit_limit", "customers.cat_tax", "customers.tax_nbr", "customers.shop_name", "customers.billing_address"}), #"Expanded customers.billing_address" = Table.ExpandListColumn(#"Expanded customers1", "customers.billing_address"), #"Expanded customers.billing_address1" = Table.ExpandRecordColumn(#"Expanded customers.billing_address", "customers.billing_address", {"id", "company_id", "address1", "address2", "city", "province", "zip"}, {"customers.billing_address.id", "customers.billing_address.company_id", "customers.billing_address.address1", "customers.billing_address.address2", "customers.billing_address.city", "customers.billing_address.province", "customers.billing_address.zip"}), #"Changed column type" = Table.TransformColumnTypes(#"Expanded customers.billing_address1", { {"customers.id", Int64.Type}, {"customers.date_entered", type date}, {"customers.name", type text}, {"customers.full_name", type text}, {"customers.phone", Int64.Type}, {"customers.customer_type", type text}, {"customers.customer_type_desc", type text}, {"customers.email", type text}, {"customers.terrritory", Int64.Type}, {"customers.rank", type text}, {"customers.sales_rep", Int64.Type}, {"customers.sales_rep_name", type text}, {"customers.vendor_nbr", type text}, {"customers.credit_limit", Int64.Type}, {"customers.cat_tax", type text}, {"customers.tax_nbr", type text}, {"customers.shop_name", type text}, {"customers.billing_address.id", Int64.Type}, {"customers.billing_address.company_id", type text}, {"customers.billing_address.address1", type text}, {"customers.billing_address.address2", type text}, {"customers.billing_address.city", type text}, {"customers.billing_address.province", type text}, {"customers.billing_address.zip", type text}, {"status", type text}, {"statusmsg", type text} }) in #"Changed column type" //---------------------------------------------------------------------- **** This version completes the table in Dataflows, but ERRORS when trying to post to my Lakehouse Dataflow ID: 4c893113-92f1-411f-835a-68eb7801be47 Session ID: d4fc34fa-7e6b-9edf-3c01-ea927ed87749 Root activity ID: e28dab67-5c68-46ae-a659-20be76b3a0e4 Time: 2023-08-21T18:23:35.257Z /*let // Define the base URL baseUrl = "http://192.000.00.18:20002/api/CUSTOMERS.PBI", // ****** Function to get data from a specific pages ******* GetDataFromPage = (page) => let // Append page number to the URL url = baseUrl & "?page=" & Text.From(page), Source = Json.Document(Web.Contents(url)), ConvertedTable = Table.FromRecords({Source}) in ConvertedTable, // ******* END NESTED FUNCTION *********************** // "PAGES" Define the number of pages available (adjust as needed) maxPages = 1, // Create a list that holds the data from all pages allData = List.Generate( () => [page = 1, data = GetDataFromPage(1)], each [page] <= maxPages, each [page = [page] + 1, data = GetDataFromPage([page])], each [data] ), // Combine all the data from different pages combinedData = Table.Combine(allData), // Expand customers #"Expanded customers" = Table.ExpandListColumn(combinedData, "customers"), #"Expanded customers1" = Table.ExpandRecordColumn(#"Expanded customers", "customers", {"id", "date_entered", "name", "full_name", "phone", "customer_type", "customer_type_desc", "email", "terrritory", "rank", "sales_rep", "sales_rep_name", "vendor_nbr", "credit_limit", "cat_tax", "tax_nbr", "shop_name", "billing_address"}, {"customers.id", "customers.date_entered", "customers.name", "customers.full_name", "customers.phone", "customers.customer_type", "customers.customer_type_desc", "customers.email", "customers.terrritory", "customers.rank", "customers.sales_rep", "customers.sales_rep_name", "customers.vendor_nbr", "customers.credit_limit", "customers.cat_tax", "customers.tax_nbr", "customers.shop_name", "customers.billing_address"}), #"Expanded customers.billing_address" = Table.ExpandListColumn(#"Expanded customers1", "customers.billing_address"), #"Expanded customers.billing_address1" = Table.ExpandRecordColumn(#"Expanded customers.billing_address", "customers.billing_address", {"id", "company_id", "address1", "address2", "city", "province", "zip"}, {"customers.billing_address.id", "customers.billing_address.company_id", "customers.billing_address.address1", "customers.billing_address.address2", "customers.billing_address.city", "customers.billing_address.province", "customers.billing_address.zip"}), // Change column schema #"Changed column type" = Table.TransformColumnTypes(#"Expanded customers.billing_address1", { {"customers.id", Int64.Type}, {"customers.date_entered", type date}, {"customers.name", type text}, {"customers.full_name", type text}, {"customers.phone", Int64.Type}, {"customers.customer_type", type text}, {"customers.customer_type_desc", type text}, {"customers.email", type text}, {"customers.terrritory", Int64.Type}, {"customers.rank", type text}, {"customers.sales_rep", Int64.Type}, {"customers.sales_rep_name", type text}, {"customers.vendor_nbr", type text}, {"customers.credit_limit", Int64.Type}, {"customers.cat_tax", type text}, {"customers.tax_nbr", type text}, {"customers.shop_name", type text}, {"customers.billing_address.id", type text}, {"customers.billing_address.company_id", type text}, {"customers.billing_address.address1", type text}, {"customers.billing_address.address2", type text}, {"customers.billing_address.city", type text}, {"customers.billing_address.province", type text}, {"customers.billing_address.zip", type text}, {"status", type text}, {"statusmsg", type text} }) in #"Changed column type"*/
