The step-by-step process below walks through connecting to data housed in Azure Blob Storage from Power BI using a SAS token. There are many ways to grab your data from Blob Storage, but this is the most efficient, scalable, and secure way that I found (with some security restrictions from watchful DBAs).
Resources Needed:
- Base URL for container
- SAS Token (must have read AND list permissions)
- Check out the link in resources for a tutorial on generating SAS Tokens.
- File Path (should end with .csv)
- Power BI Desktop
Notes:
- You can skip ahead to the sample M script if you have all your elements. Simply swap out the BaseURL, SASToken, and FilePath and you’re good to go. Otherwise, feel free to walk through the steps below to gain a deeper understanding of the process.
- Make sure your Base URL ends with a “/”, your SAS Token starts with “?”, and your file path ends with “.csv”
- Keep the double quotes around each parameter value, this forces Power BI to recognize it as text.
Process:
- In Power BI Desktop, go to Get Data and select the Web option.
- Switch to the advanced view and put the base URL in the first box.
- Put in the second box the SAS token.
- In a third box (click add part to get the third one), put “&restype=container&comp=list” (this will allow you to list all the blobs in that container).
- Expand the blob down then filter the name on the file path.
- Create a custom column to create the entire URL for the file (M code samples are below).
- FileURL = BaseURL & [Name] & SASToken
- Create another custom column to access the web contents of your FileURL column.
- BinaryURLContents = Web.Contents([FileURL])
- Remove all columns except the BinaryURLContents.
- Click on the “Binary” value and watch Power BI expand out your CSV file.
- Manipulate data from there as needed.
Final M Code:
let
BaseURL = "BASE_URL_HERE"
,SASToken = "SAS_TOKEN_HERE"
,FilePath = "FILE_NAME_HERE_(Note do not include section of the URL from Base URL)"
,Source = Xml.Tables(Web.Contents(Text.From(BaseURL) &Text.From(SASToken) & "&restype=container&comp=list")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Blobs"}),
#"Expanded Blobs" = Table.ExpandTableColumn(#"Removed Other Columns", "Blobs", {"Blob"}, {"Blob"}),
#"Expanded Blob" = Table.ExpandTableColumn(#"Expanded Blobs", "Blob", {"Name", "Properties", "OrMetadata"}, {"Name", "Properties", "OrMetadata"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Blob", each ([Name] = Text.From(FilePath))),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "FileURL", each BaseURL & [Name] & SASToken),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "BinaryURLContents", each Web.Contents([FileURL])),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"BinaryURLContents"}),
BinaryURLContents = #"Removed Other Columns1"{0}[BinaryURLContents],
#"Imported CSV" = Csv.Document(BinaryURLContents,[Delimiter=",", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
#"Promoted Headers"
//Use this query to validate your file path
let
Source = Xml.Tables(Web.Contents("BASE URL" & "SAS TOKEN" & "&restype=container&comp=list")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NextMarker", type text}, {"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Blobs"}),
#"Expanded Blobs" = Table.ExpandTableColumn(#"Removed Other Columns", "Blobs", {"Blob"}, {"Blob"}),
#"Expanded Blob" = Table.ExpandTableColumn(#"Expanded Blobs", "Blob", {"Name", "Properties", "OrMetadata"}, {"Name", "Properties", "OrMetadata"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Blob", each [Name] = "FILE PATH")
in
#"Filtered Rows"Additional Resources:
