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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mwaltercpa
Advocate III
Advocate III

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

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
DennesTorres
Solution Supplier
Solution Supplier

Hi,

What's the error message?

Kind Regards,

 

Dennes

Error DF.PNG

 Please see attached.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 FBC Gallery Image

Fabric Monthly Update - March 2024

Check out the March 2024 Fabric update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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