Read & Manipulate Delta Table from Lakehouse in Excel via Power Query

[JoachimSA]

Hi everybody,

I have a scenario where I produce a table of 1000 - 2000 Rows in a Fabric Lakehouse using a Fabric Notebook.

I now want to manipulate individual Cells and load that manipulated table back into the Lakehouse.

It seems that Excel would be the best tool because of easy filtering and conditional formatting to help with the readability and ease of use finding the rows you want to manipulate.

I already found Chris Webbs' Article from back in December on this and used his code to connect to a folder in a Fabric Lakehouse using AzureStorage.DataLake:

let
    Source = AzureStorage.DataLake("https://onelake.dfs.fabric.microsoft.com/insertworkspaceidhere/insertlakehouseidhere/Tables/DeltaCoffee/", [HierarchicalNavigation=true]),
    ToDelta = DeltaLake.Table(Source)
in
    ToDelta

The URL i got from the properties of the delta table in my lakehouse (structered like the one in Chris' code).

Now I get an Expression Error:

"The Name "DeltaLake.Table" was not recognized. Is it spelled correctly ?
(translated from german)

Is this Deltalake.Table Function not available in Powerquery in Excel ?

I have Microsoft 365 Enterprise, Excel Version 2401 (Build 17231.20236)

Thanks for your help !

[jack533]

It appears that there is currently no direct method for importing Deltatable data into Excel.

If Microsoft intends to use DeltaLake, it would be extremely interesting to know. Additionally, table becomes PQ in Excel. Would be greatly appreciated: A faint smile on the face:

I assisted myself by obtaining the Delta Table using a Dataflow Gen1 and connecting to it in Excel using PQ. It seems needlessly complicated and one jump too many.

In order to access that directly with PQ in Excel, I also considered writing a.csv or possibly even an Excel file into the Lakhouses Files Area using the Notebook that is building the Delta Table.

I haven't yet attempted that.

[JoachimSA]

Is there still no way to directly import Delta tables using the DeltaLke.Table function in Excel like in Power BI ?

[Anonymous]

I am attempting to connect to the Fabric Lakehouse via a SQL Endpoint.  I have followed the instructions in this thread without success.  I get the following error when I attempt to connect.  Is this type of functionality available in Excel?  It should be.

[JoachimSA]

there is still no update on accessing Delta Tables in a Fabric Notebook in Excel right ? Still no way around using the SQL endpoint ?

[StephB]

I had the same issue here, and I solved it by setting the autentication mode in data source parameters from "Get Data"  button.

I switch it from Windows to Microsoft account

Sorry in advance, we are using French version of Office.

1-

2- 

3 - 

[JoachimSA]

It does seem that there is no direkt way to get Deltatable into Excel at the moment.

I would be very interested to know whether Microsoft is planning to implement the DeltaLake.Table into PQ in Excel as well. Would be very welcome 🙂

I helped myslef by using a Dataflow Gen1 to get the Delta Table and then connecto to it with PQ in Excel. Seems unnecessarily cumbersome with 1 hop to many.

I also thought about using the Notebook that is creating the Delta Table to also/instead write a .csv or maybe even Excel into the lakhouses Files Area to access that directly with PQ in Excel.

Havent tried that yet.

I would be very happy to hear any suggestions on how to make this a less cumbersome endeavour 😉

Best regards,

Joachim

[Anonymous]

Hi @JoachimSA 

The function is not available in Power Query in Excel currenlty. It is only available in Power Query in Power BI Desktop and in Dataflows Gen1 and Gen2. 

For the SQL endpoint connection, this is available in Excel. You can get a SQL connection string through the following method:

Paste above connection string into the Server Name. Leave Database and SQL statement blank. Click OK. 

After login in with your Microsoft account, you are able to get the Navigator window. You can then select the delta table you want under a specific lakehouse. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[JoachimSA]

Thanks for that answer. I tried it exctly as you described but before getting to the Micrososft Account Login i get the error message: "Microsoft SQL: Integrated Security not supported"

Is there any security setting on the excel side or Lakehouse side that I should play with ?

Thanks,

Joachim

[Anonymous]

Do you select "Microsoft account" type in this authentication window?

[JoachimSA]

the error message is popping up before I get to that screen:

Sorry for the German 😉 Title says: Connection not possible

[JoachimSA]

I meanwhile tested to use the SQL endpoint like in this Video by Baker Tilly US 
From within Excel tried to connect to a SQL server using a SELECT * FROM TableName 

But as you can see I have to input something into the "Database" input.

If i just put something random, I get a 
: "Microsoft SQL: Integrated Security not supported."
Error...

There should be some way to get a Fabric Delta Table into Excel, right ??

Thanls for the help