I am using the Web API datasource to connect to a JSON source table via Dataflows Gen2. If I leave the M code as is, I get the first page (50 records) in my preview. I am then able to Publish to my lakehouse and view the table as a delta formated table.
However, when I add a M function to page through more than the first page (50 records), even though I am still able to view the exact same result in my preview window (50 records, limited to just page 1), I am unsuccessful in posting this same table, same schema etc. to my lakehouse. Using the later M code, I get an error when publishing to the lakehouse.
Here are the two different versions of my M code, the first works, the second fails. But they both succesffuly display in the DF preview.
Please note that I masked my URL with zeros.
**** 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"*/
