Read data from delta table from Azure Gen 2 Storage

Delta Table is opensource table format which is based on Apache parquet file system. It supports ACID properties like any relational database. Delta tables also supports versioning for time travel to previous version if any error occurs. In Delta table folder there is folder named delta log which contains json file which encompass meta data for table.

STEPS to read delta table from Azure Storage Account:

Method 1:

  1.  In PowerBI desktop, go to Home -> Queries -> Transform Data 
  1. Once you are in the Power Query Editor use Home -> New Source -> Blank query 
  1. Go to Home -> Query -> Advanced Editor 
  1. Paste the code of the custom function: fn_ReadDeltaTable.pq and name the query fn_ReadDeltaTable

 

  1. After pasting code in Advanced Editor create new blank query and paste following code and Connect to your Storage Account 

let 

    Source = fn_ReadDeltaTable( 

    AzureStorage.DataLake( 

        “https://yourstorageaccountname.dfs.core.windows.net/curated/store/sales/Measure/CartItem”,  

        [HierarchicalNavigation = false])) 

in 

    Source 

Note: URL contains Path to Delta Table

6. We can see that data gets loaded

Method 2  :

  1. Repeat till Step 4 
  1. After pasting code in Advanced Editor create new blank query and paste following code 

and Connect to your Storage Account 

let 

    Source = AzureStorage.Blobs(“https://yourstorageaccount.blob.core.windows.net/curated”), 

    #”Filtered Rows” = Table.SelectRows(Source, each Text.StartsWith([Name], “/store/sales/Measure/OrderItem/”)) 

in 

    #”Filtered Rows” 

Note: is container URL 

           /store/sales/Measure/OrderItem/ is delta table path

 

3. Go to  fn_ReadDeltaTable query and Under DeltaTableFolderContent select the Required query and click invoke

4. We now get our data loaded 



Aniket Ghodinde
Data Engineer
Addend Analytics

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.

Email Us Or Contact Us