Connecting an ADLSgen2 to Power BI Service for Power BI Dataflows

If you would like to have Azure Data Lake Storage generation 2 (AKA StorageV2) implemented within your power BI Dataflows, I’ve created a step-by-step guide on how to do so. Follow along with these steps and you should be on your way to implementing ADLSgen2 with Power BI Dataflows in no time!

Create the Storage Account

https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-quickstart-create-account

Before you create an account, you first create a resource group that acts as a logical container to storage accounts or any other Azure resources you create.

Note: You must create new storage accounts as type StorageV2 (general-purpose V2) to take advantage of Data Lake Storage Gen2 features.

When naming your storage account, keep these rules in mind:

  • Storage account names must be between 3 and 24 characters in length and may contain numbers and lowercase letters only.
  • Your storage account name must be unique within Azure. No two storage accounts can have the same name.

Sign in to the Azure portal.

Create a resource group

To create a resource group in the Azure portal, follow these steps:

  1. In the Azure portal, expand the menu on the left side to open the menu of services, and choose Resource Groups.
  2. Click the Add button to add a new resource group.
  3. Enter a name for the new resource group: “pbidataflows”
  4. Select the subscription in which to create the new resource group: “PWTraining”
  5. Choose the location for the resource group: (US) North Central US
  6. Click the Review & Create button, then the Create button.

Create a general-purpose v2 storage account

  1. In the Azure portal, expand the menu on the left side to open the menu of services, and choose All services. Then, scroll down to Storage, and choose Storage accounts. On the Storage Accounts window that appears, choose Add.
  2. Select your Subscription and the Resource group you created earlier.
  3. Enter a name for your storage account: dataflowspbi
  4. Set Location to North Central US
  5. Leave these fields set to their defaults: Performance, Account kind, Replication, Access tier.
  6. Choose the subscription in which you want to create the storage account.
  7. Select Next : Advanced >
  8. Leave the values under SECURITY and VIRTUAL NETWORKS fields set to their defaults.
  9. In the Data Lake Storage Gen2 section set Hierarchical namespace to Enabled.
  10. Click Review + Create to create the storage account.

THERE ARE TWO OPTIONS TO CREATE BLOB/FILE SYSTEM: HERE ARE BOTH WAYS. ALSO NOTE THAT BOTH OF THESE METHODS REQUIRE DOING SOMETHING EXTRA TO MAKE IT WORK. IN STEP 1 YOU CAN CREATE A FILE SYSTEM BUT ALSO NEED TO ADD A READER ROLE IN THE SAME LOCATION. STEP 2 YOU CAN CREATE A FILE SYSTEM BUT ALSO NEED TO ADD PERMISSIONS AT THE OBJECT ID LEVEL. I AM GOING TO LEAVE IT UP TO YOU WHICH WAY YOU WANT TO CREATE THE FILE SYSTEM, BUT I RECOMMEND ONLY CREATING THE FILE SYSTEM FROM STEP 1 AND GOING SEQUENTIALLY.

You must create a file system named powerbi before your storage account can be added to Power BI. There are many ways to create such a file system, including using Azure Databricks, HDInsight, AZCopy, or Azure Storage Explorer.

1) Create a File System for Power BI & Grant the Power BI service a reader role

  1. In the Azure portal, navigate to your storage account and display the Overview for the account.
  2. Under Data Lake Storage, select File Systems
  3. Hit +File system button to create a new File System: powerbi
  4. Select Access control (IAM) to display access control settings for the container. Select the Role assignments tab to see the list of role assignments.
  5. Click the Add role assignment button to add a new role.
  6. In the Add role assignment window, select the Reader role to assign to the Power BI service. Then use search to locate Power BI Service. The following image shows the Reader role assigned to the Power BI service.
    Allow at least 30 minutes for permission for propagate to Power BI from the portal. Any time you change permissions in the portal, allow 30 minutes for those permissions to be reflected in Power BI.

2) Create a file system for Power BI & Grant Power BI Permissions to the file system

This step requires that you install Azure Storage Explorer version 1.6.2 or higher. To install Azure Storage Explorer for Windows, Macintosh, or Linux, see Azure Storage Explorer.

  1. Once you’ve successfully installed Azure Storage Explorer, on first launch, the Microsoft Azure Storage Explorer – Connect window is shown. While Storage Explorer provides several ways to connect to storage accounts, only one way is currently supported for the required setup.
  2. In the left pane, locate and expand the storage account you created above.
  3. Right-click Blob Containers, and – from the context menu – select Create Blob Container.
  4. A text box will appear below the Blob Containers folder. Enter the name powerbi (you already did this in the first create a file system step)
  5. Press Enter when done to create the blob container

In the next section, you grant the Power BI family of services full access to the file system you created.

Grant Power BI permissions to the file system

To find your tenant applications, follow these steps:

  1. In the Azure portal, select Azure Active Directory from the left navigation panel.
  2. In the Azure Active Directory blade, select Enterprise applications.
  3. From the Application Type drop-down menu, select All Applications and then select Apply. A sample of your tenant applications appears.
  4. In the search bar, type Power and a collection of Object IDs for Power BI and Power Query applications appear. You’ll need all three values in the subsequent steps.
  5. Select and copy both Object IDs for Power BI service and Power Query online from the results of your search. Be ready to paste those values in subsequent steps.
  6. Next, use Azure Storage Explorer to navigate to the powerbi file system you created in the previous section. Follow the instructions in Managing access section of Set file and directory level permissions using Azure Storage explorer article.
  7. For each of the two Power BI Object IDs collected in step 5, Assign Read, Write, Execute Access and Default ACLs to your powerbi file system.
  8. For the Power Query Online Object ID collected in step 4, Assign Write, Execute Access and Default ACLs to your powerbi file system.
  9. In addition, for the Other, Assign Execute Access and Default ACLs as well.

Connect your Azure Data Lake Storage Gen2 to Power BI

Once you’ve set up your Azure Data Lake Storage Gen2 account in the Azure portal, you connect it to Power BI in the Power BI admin portal. You also manage Power BI dataflow storage in the Dataflow storage settings section of the Power BI admin portal. For guidance on launching and basic use, see How to get to the admin portal for detailed information.

You connect your Azure Data Lake Storage Gen2 account with the following steps:

1. Navigate to the Dataflow settings tab of the Power BI admin portal

2. Power BI admin portal

3. Select the Connect your Azure Data Lake Storage Gen2 button. The following window appears.

4. Azure Data Lake Storage Gen2

5. Provide the Subscription ID of the Storage Account.

6. Provide the Resource Group name in which the storage account was created.

7. Provide the Storage Account name.

8. Select Connect.

And you’re done! Now you should have the integration you’re looking for!

Resources: https://docs.microsoft.com/en-us/power-bi/service-dataflows-connect-azure-data-lake-storage-gen2

Advertisements

Nick is Coming to Town! I’ll be speaking at SQLSaturday#820 in Jacksonville on May 4th!

I’ll be teaching:
Making Your Queries Dynamic in the Power Query Editor for Power BI.

If you’d like to learn more, come see me in room:
Jedha – 4-1702
Note: Assigned room is subject to change

The session is from 9:00 to 9:45 AM.


See you there!

The Easy Way to Duplicate Tables in Power BI

Duplicating tables within Power BI can be very useful for certain situations. Those situations may be because you want to utilize role-playing dimensions without having to utilize inactive relationships or to modify/filter the same dataset, just differently.

The most intuitive way to duplicate a table in Power BI is just to reload the data source using the Get Data option. Even though this method works, it then excludes any changes you made to the table in the Power Query Editor and you would have to re-do all of those steps.

Now for The Easy Way.

The easiest way to duplicate tables is actually inside the Power Query Editor. See the screenshot below for the dataset I’ll be working with.

This is the AdventureWorks dataset and we’ll be focusing on the Date table.

In this scenario, I want to duplicate the Date table to fulfill role-playing capabilities without using inactive relationships. Notice that there are several transformations that have been done to this table besides the implicit ones (Renamed Columns, Removed Columns, Filtered Rows).

Method 1

The first method is to highlight the Date table, and copy and paste the table with CTRL+C and CTRL+V. This created a second Date table called: Date (2), as it’s a direct copy of the first Date table.

Note that all of the Applied Steps remain exactly the same.

This method even copies all of the M code/Transformations/Applied Steps that were used in this table and you can modify those steps as necessary to fit your needs for this new table.

Method 2

The second method is to right click on the Date table and choose “Reference”.

This also creates a direct copy of the Date table… EXCEPT the M code/Transformations/Applied Steps that were previously applied ARE NOT modifiable.

Notice that the table named Date (3) (which is the table created using the Reference method) as an empty Applied Steps section, even though the applied transformations are still affecting this table (the renamed columns, the removed columns, and the filtered rows steps have still taken place)

This second method is ideal for creating an exact duplicate of a table that has transformations applied to it that you do not want to change in the future.

Thanks for tuning in to this blog on the easiest way to duplicate tables!

Don’t forget to follow me on twitter, @backtosql !

Q&A from Webinar on 5/15/18. Creating Your Power BI Reports with “WOW”

Do templates save roles and their filters for row-level security?

Yes, templates do save roles and their filters for RLS. Templates save all the metadata in a report.

What version is this?

I am currently running the May release of Power BI Desktop.

Why not use the page level filters to make more room on the report rather than using slicers?

When viewing reports, oftentimes end users want to be able to slice the data themselves to gather the information they want. It is more optimal to have end users use slicers than create new page level filters themselves.

How do you remove the “Power Tips” logo from the bottom of the layout?

That logo is a part of the background image that is loaded in the template. You would have to remove that from the image itself.

Older people have issues viewing gray. Do you suggest changing the default gray objects to black as a default?

Absolutely! Within custom themes/templates, I highly recommend changing default colors to something like black, as well as raising the default font size from 8 to maybe 11 or higher.

Can we create Adhoc Reports using Power BI?

As long as you have access to the Dataset within the Workspace, yes, you can create Adhoc reports from the Power BI Service itself.

With Dashboard Themes, do they override the formatting of the tiles, too?

Yes, that is correct. The visualizations in the dashboard get changed to the new theme. The same visualizations in the report remain the same.

How do you show only the relevant data on slicers if we use a dimension attribute?

There are a couple of solutions to this: 1) Create a page/report level filter that only has the relevant dimensions or 2) do an Inner join on the tables you are concerned about to only show the relevant data.

State, City are two different slicers, Assume Same City in two states, so here’s my question, if I select city, how can we pass selected city?

There are two options: 1) Create a geographical Hierarchy and drilldown your visuals to the City level or 2) you would need to model your data to include both City and State in the same field (e.g. Jacksonville, FL rather than Jacksonville), or even include latitude/longitude values to be more precise. If you use the former method, you need to classify the columns as a “Place” rather than a “City” or a “State”, because it is both a city and a state.

Within the Selection Pane, is it possible to change the name of the five slicers to slicerColor, slicerGender, etc. instead of just slicer?

In the format settings of the visualization, you can title your slicers to whatever you’d like to name them. The title doesn’t have to be enabled for the name to change just in the Selection Pane.

Does the file work with Power BI Report Server?

Everything that was demonstrated is currently usable within Power BI Report Server version except for using custom themes. That is still a preview feature within the Cloud version of Power BI Desktop.

Using Themes to Enhance your Power BI Reports

Hey everyone!
I’ve created this blog today to show you how you can spice up your Power BI Reports and Dashboards by using a preview feature within Power BI called Custom Report Themes. Currently, this feature is only available within the Power BI Desktop cloud version, not the Power BI Desktop version for Report Server.

Whether you want to create a color scheme that matches your company’s colors, format your text options like fonts/font size/font color, or maybe you  just want to have your visualizations look cleaner, crisper, and stand out from the rest. Regardless of the reason, using Custom Report Themes is an easy way to accomplish these types of goals.

First and foremost, you need to enable custom themes in Power BI Desktop as a preview feature. To do so, Select File>Options and Settings>Options.

image

Then select “Preview Features” and enable Custom Report Themes. Then select OK. This will require a restart of your Power BI Desktop.

image

Now you have the ability to use Custom Themes. You can validate this by ensuring you have the “Themes” section with the “Switch Theme” button visible on your Home Ribbon of Power BI Desktop.
image

Themes use a certain file type. This file type is called .JSON . It looks a little intimidating at first, but in reality it’s not bad, especially if you have the proper tools to create a .JSON file.

I have a few different theme-ing tools that I like to use.

Firstly there’s this easy to use tool made by Petri Software that is available from this link.
https://community.powerbi.com/t5/Community-Blog/How-to-use-your-Office-Theme-as-Power-BI-Report-Theme/ba-p/210813
This tool is a macro enabled excel file that can convert office themes/colors to .JSON files and you can use those in your power BI Reports. Petri Software did a great job making it intuitive to use, it has step by step instructions on how to use it in the excel itself. This is what it looks like.image

The Second tool that I’d like to show you is called the Report Theme Gallery. This is a collection of different themes that other members of the Power BI Community have created and shared. Find the Gallery at this link https://community.powerbi.com/t5/Themes-Gallery/bd-p/ThemesGallery?sortby=kudos

When you select a theme that looks good from this site, there is a .JSON file that the creator has included that is downloadable. For instance, I like how the theme called “University of Melbourne” looks, so I selected that theme and can download the .JSON file for that theme using the provided link.
image

The Third tool that I’d like to show you is one called the Report Theme Generator (v3). This is made by the very talented folks over at powerbi.tips. This tool allows you to customize your theme on a grand scale. You can not only set global data colors with it, but also global table settings, font settings, and visual per visual settings. The visual per visual settings contain basically everything that you would find in the “Formatting” paint brush you’d see in the visualization pane of Power BI Desktop. Being able to create defaults for every visual is extremely powerful. Find the tool here: https://powerbi.tips/tools/report-theme-generator-v3/

The only tricky part to this tool is setting your global data colors, which really isn’t very difficult. Select the colors you want on the color wheel and select the “Add Selected Color” button in the appropriate section.

image

After selecting all the formatting options you’d like to include in your theme, then select the Download button underneath the color wheel and you have your .JSON file to load into Power BI.

The Fourth and final tool I’d like to show you is one made by Charles Sterling, aptly named the Charles Sterling Theme Creator. This tool used to be hosted on an Azure website, but as of now it is downloadable via github and can be used offline as an .html file. Find it here: https://github.com/CharlesSterling/themecreator

This tool uses a type of color wheel that automatically helps create an expansive color schema that is nearly usable out of the box. With a little tweaking, it’s easy to get exactly what you’re looking for. Once you’re finished getting the color schema you’re looking for, select ‘Apply’ in the top right corner and you’ll have your .JSON file ready to import into Power BI Desktop.

image

Now, using one of these tools, we are ready to import a .JSON file into Power BI. To do so, click on that new “Switch Theme” button we got to populate via Preview Feature and select “Import Theme”.
image

Then choose the .JSON file type that you created, and voila! Your theme is now imported into your report automatically and will automatically apply to any of your existing visuals.

If you have any questions, comments, or concerns, feel free to reach out to me in any of the available portals.

Thanks for reading!

Questions and Answers from Webinar–Training on the T’s–Introduction to DAX 4/17/18

Can the search/find function reference a predefined list or named category that includes several key words?
Not inherently, no. What you may be able to put together is a formula that utilizes the LOOKUPVALUE function to find exactly what you need.

What if you wanted to sum profit/sales/costs/etc. for a particular product or business unit?
In a visual, like a table for instance, you can just drop in the fields that you want after you create the measures, and apply the measures as well. Once you do so, the measure will filter to whatever the context is on the table and you’ll be able to get your total profit/sales/costs/etc. for every product listed.

How did you hide columns?
In Power Query Editor, you can Right Click on the column(s) you want to hide and select “Hide Column”.

When calculating someone’s exact age, what about leap years?
The formula we used for calculating someone’s exact age will still provide the same result, regardless of the birth date.

Can we get this presentation after class?
Yes, all of the webinars we create are visible for free on pragmaticworks.com. Direct link to all of the Training Courses we’ve done so far are found here: https://pragmaticworks.com/Training/Courses

I think an easier to understand dax formula for calculating the age would be: ROUNDDOWN(DATEDIFF(Table1[Birthday],Table1[Today],MONTH)/12,0).
The example you gave is a good example and could be something we add to our DAX Cheatsheet or something we even slide into the Advanced DAX class as an alternative method of doing something while introducing functions we may not have discussed. That being said,

  1. Conceptually I think the one we use is a little easier to understand and work through.
  2. The one we use creates a reusable pattern that can be used for other things, not just the birth date.
  3. The primary reason we used the other expression is because it allows us to show off multiple things in DAX. For example the longer example we use shows how to use conditional logic (IF statement), the DATEDIFF function, and the FORMAT function.

Is this training part of Power BI training package?
This is scratching the surface of our Introduction to DAX course we have available in our On Demand Training platform. While this was a 1 hour course, our On Demand Intro to Dax course has over 5 hours of content + Quizzes per module + labs.

Are all text functions one based and not zero based? In other words, the first character is 1 and not 0?
Yes, Power BI’s first character is always “one based”, so the first character is always referenced as the number 1 rather than 0.

Is it necessary to include line breaks in the formulas?
Nope! Line breaks are not necessary at all. But, when you get to creating more complex DAX calculations, especially ones that may need to be updated at some point, it is much easier to read and comprehend what the formula is exactly doing with line breaks included.

Can we get access to the slide deck?
Sure, here is a link where you can download the slide deck. https://file.ac/OOOsG3BFZmU/DAX%20Intro%20Slides2.pdf

Please explain the difference between count and countx again thanks!
COUNT is specific to a single column, and it counts the number of rows that contain either numbers, dates, or strings. If the row contains text that cannot be traslated to a number, then the row is not counted. The COUNTX function allows us to extend the operation of COUNT, and take an expression (as an arugment) that is evaluated over a table. By doing so, COUNTX enables us to perform independent calculations and subsequently take a count of the rows that translate to a number/date.

Does it support regex?
DAX does not have any dedicated functions for RegEx. To use RegEx, you will need to be using R scripting.will there be any sample data provided for testing?All of the scenarios we went over on this course are usable for any type of datasets that contain birthdates and sales information.

Removing Links from Power BI Dashboard Tiles

A common issue that developers have when creating dashboards for end users who should simply see dashboards, and not reports themselves, is that when a dashboard visual is clicked, there is automatic routing to the report for the respective pinned visual.

Below is both a video tutorial as well as a text/image based tutorial showing how to accomplish our goal.

This is an example dashboard that shows what our current issue is.

gif2

There is no inherent way to stop this interaction from happening. Though, what does exist, is a workaround to stop this interaction. This is accomplished by pointing that tile to an “external link” (as it’s called in the Power BI Service) and having it redirect back to the dashboard itself.

To accomplish this, the first thing you need to do is copy the dashboard URL. Simply highlight the URL in your dashboard and right click and copy, or alternatively select CTRL+C.

Next, you want to click on the ellipses corresponding to the dashboard visual/tile that you don’t want going to your report and select “Edit details”

edit details

 

Once you select edit details, the Tile Details window appears. Then choose the options “Set custom link”, “Link type: External link”, and “Open cusom link in the same tab?: Yes”. Lastly, paste the URL into the URL section of the Tile details window, then select apply.

imageimage image

What this accomplishes is almost exactly what we want, which is that it redirects the click on the tile to the dashboard page. This is a step in the right direction, and is a pretty intuitive solution on its own, but it still annoyingly refreshes the entire page.

refresh

We can take this one step further and actually stop the page from reloading every time this action is taken. It isn’t an intuitive workaround, but it does work.

Go back to Tile Details pane (by selecting the ellipses of your desired tile and selecting “Edit details”) and add a hashtag/pound sign to the end of your URL that you pasted, like so.

image

Now when you go to click on your desired tile, it just remains on the same page exactly as is, not refreshing or reloading anything.

no refresh

With this workaround, you’ll have to set this custom URL for every tile in your dashboard that you don’t want redirecting to your Report. As I said before, it’s not an inherent feature, but it works!