Web API in Dataflow Gen2 fails on publish to my Lakehouse?

mwaltercpa — Mon, 21 Aug 2023 23:47:13 GMT

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

Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse?

DennesTorres — Tue, 22 Aug 2023 15:59:43 GMT

Hi,

What's the error message?

Kind Regards,

Dennes

Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse?

mwaltercpa — Tue, 22 Aug 2023 20:19:29 GMT

Please see attached.

Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse?

DennesTorres — Wed, 23 Aug 2023 00:37:56 GMT

Hi,

It took me a while to notice: The error message says "Dataflow Publish failed".

This means the error happened while you were publishing the dataflow, not while it was being executed. (I believe for the execution the error would be different).

It can be something temporary, or an issue with Fabric, or some feature not supported yet. I would suspect the M code, but you highlighted a lot how it works in preview. But some feature you are using on this M code may not be well accepted by Fabric at the moment.

Maybe a support ticket to help you find exactly what?

Kind Regards,

Dennes

topic Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse? in Dataflow

Web API in Dataflow Gen2 fails on publish to my Lakehouse?

Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse?

Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse?

Re: Web API in Dataflow Gen2 fails on publish to my Lakehouse?