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"*/
