So, it has been a while. But finally I got the time to start writing again. It has been to long, I know….
This time, a topic about PowerBI and specifically on how to connect your data model to a Data Lake Store(Gen2) in the cloud. I needed it for a bit of data exploration, and found a way on how to connect and explore data. So let us begin, as always, feel free to comment/leave a question in the remarks.
Let me first show you the data that we will be working with in this blog. Below an image of the files currently in the DLS.(Microsoft Azure Storage Explorer):
As you can see, the files are about Star Wars. One of my personal favorites. I know, how typical right? Well it’s just wat it is really. Ok, let’s move on. So the goal is to explore these files, located in the DLS, with PowerBI. So let’s start PowerBI Desktop and choose ‘Get Data’(Gegevens ophalen for the dutchies). Then select ‘Azure -> Azure Data lake Storage Gen2’. PowerBI then asks you to give the URL to your DLS and click OK, in my case(https://yourstorageaccount.dfs.core.windows.net/cnt-starwars:
Then PowerBI will show you a list of files in the DLS. Choose Load.
Now you can rename the query(and it’s folder). In my case, I named it: STG-DLS-All files.
This query will be the base for all of the other queries in the model. In the query overview we will create a new query by right clicking in a blank space and choosing the New Query à Empty query option.
Fill in the the formula: “= #”STG-DLS-All files””.
The list of files are shown. Click on “Binary” for the file you want to explore and add to your model.
And that’s it! You can choose to take the first row as header for the column names, if the datasets allows it of course.
In this case I did, I also renamed the query to give it a meaningful name. I also hid the STG-DLS-All files query in the model. Now Save and exit, and check out the results!
