Change the Excel source to an azure blob storage

[Anonymous]

Hi guys,

you can change the source in the Power Query from an Excel file to an Azure Blob storage. The structure remains exactly the same - only the source changes?

thanks in advance

greetings lukas

[edhans · Accepted solution]

Hi @Anonymous - you didn't say how you were connecting to the Excel file - local drive, SharePoint, etc. However, the change is pretty straight forward.

If you open the advanced editor of your Excel queries you might see something like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\OneDrive\Purchasing Wait List.xlsx"), null, true),
    OldBookList_Table = Source{[Item="OldBookList",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(OldBookList_Table,{{"Date", type date}, {"Title", type text}, {"Author", type text}, {"Category", type text}, {"Priority", Int64.Type}, {"Comments", type text}, {"Date Started", type any}})
in
    #"Changed Type"

What you need to do is connect to the Azure Storage Blob the way you normally would, then get the query editor down to the same place as where I have the #"Changed Type" step, as that is where the real work begins.

Then, you copy those first 2-3 Azure Storage lines in the advanced editor and replace the first 2/3 lines of your Excel queries. A few notes:

1. The lines you copy must all have a comma at the end. The last step may not, but if copy that last Azure Storage Blob step, manually add a comma
2. If the step names change, say at line 4, make sure the table name referenced in line 4 is what is shown in line 3. So in my step above I have a step called OldBookList_Table. Maybe when I connect with ASB it is called #"Create connection" - (I made that up, this is an example. So the Changed Step would need to remove OldBookList_Table and replace it with #"Create connection." The #"name here" syntax is needed if there are spaces or special characters in the step names.

See the instructions below too as they can walk you through the process.

See this article if you need help using this M code in your model.

If you need specific help, do the following:

• Post the M code for your Excel files here, using the code box </> icon in the ribbon
• Post the M code for your ASB queries here using the same code box.

I or someone else can assist in merging the code.