Do you want to learn how to contain your ETL within Power BI? In a recent webinar with Consultant Nick Lee you’ll learn some tips and tricks when it comes to setting up your ETL process within Power BI. ETL inside of Power BI gives us self service capabilities. These self service capabilities are what many people in IT, finance and marketing strive for today and the reason why many are moving to Power BI.
If you don’t know ETL (Extract, Form, Load) is the process of getting data from its source and putting it into a report and making it usable or actionable. Extract is pulling the data out of the source, transform is adding any business rules we need to the data, and load is the loading of that data into a consumable report.
This webinar is spent on a step by step demo of the ETL process within Power BI. If you’ve used a cube before, you can build cube, which is multi-dimensional model, inside of Power B. All processes you may know like star and snowflake schema and the Kimball method can all be done at once inside of Power BI.
If you’d like a walk through demo of how to contain your ETL within Power BI, then this webinar is for you. You can watch the complete webinar below.
We all know there are lots of data sources; flat files like Excel files, CSVs that you import from another data source, connecting directly to a SQL Server or to SharePoint, or using a data warehouse architecture, as well as many others. The question is, are you using too many data sources with Power BI?
One problem we see as consultants is some companies use far too many data sources. Let’s say a company is connecting to a couple different tables off of SQL Server, they’ve got on view on SQL Server and they’re connecting directly to a SharePoint list to pull in some data and exporting files from Excel files; you get the picture, right?
This can be problematic when trying to put all these different data sources into one report. If you have multiple people creating reports based on a hodgepodge of different data sources, there may be some very different types of filtering that gets done, along with different types of DAX or M code that gets used. This can cause serious ‘one version of the truth’ problems.
I suggest a best practice is to have a long-term goal – this could be months or even a year or more – for getting your data sources in one place. This long term goal can be things such as connecting to a data warehouse or Tabular model or getting a multi-dimensional cube spun up; something that contains all your data and data sources in one spot.
That one spot is where you:
Apply all your changes to these sources like any kind of DAX calculation that anyone would need is already there.
Any kind of relationship that needs to be built, active or inactive, between any table is already set up.
Ways to navigate through relationships that are inactive using certain calculations, DAX or others, are ready.
Doing this will give you one version of the truth throughout your company. Also, you will be connecting to one single data source when connecting to these via live connection. It will help prevent users from having access to data that others don’t (assuming they are supposed to have access).
In short, set your company up with a long-term goal for a type of data warehouse platform.Any person that creates reports will be able to connect to this and know they are getting one version of the truth, so they can create the reports they want with confidence.
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!
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.
To create a resource group in the Azure portal, follow these steps:
In the Azure portal, expand the menu on the left side to open the menu of services, and choose Resource Groups.
Click the Add button to add a new resource group.
Enter a name for the new resource group: “pbidataflows”
Select the subscription in which to create the new resource group: “PWTraining”
Choose the location for the resource group: (US) North Central US
Click the Review & Create button, then the Create button.
Create a general-purpose v2 storage account
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.
Select your Subscription and the Resource group you created earlier.
Enter a name for your storage account: dataflowspbi
Set Location to North Central US
Leave these fields set to their defaults: Performance, Account kind, Replication, Access tier.
Choose the subscription in which you want to create the storage account.
Select Next : Advanced >
Leave the values under SECURITY and VIRTUAL NETWORKS fields set to their defaults.
In the Data Lake Storage Gen2 section set Hierarchical namespace to Enabled.
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
In the Azure portal, navigate to your storage account and display the Overview for the account.
Under Data Lake Storage, select File Systems
Hit +File system button to create a new File System: powerbi
Select Access control (IAM) to display access control settings for the container. Select the Role assignments tab to see the list of role assignments.
Click the Add role assignment button to add a new role.
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.
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.
In the left pane, locate and expand the storage account you created above.
Right-click Blob Containers, and – from the context menu – select Create Blob Container.
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)
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:
In the Azure portal, select Azure Active Directory from the left navigation panel.
In the Azure Active Directory blade, select Enterprise applications.
From the Application Type drop-down menu, select All Applications and then select Apply. A sample of your tenant applications appears.
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.
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.
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.
For the Power Query Online Object ID collected in step 4, Assign Write, Execute Access and Default ACLs to your powerbi file system.
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!
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 Referencemethod) 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 !
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.
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.
Then select “Preview Features” and enable Custom Report Themes. Then select OK. This will require a restart of your Power BI Desktop.
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.
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.
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.
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.
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.
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”.
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.
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,
Conceptually I think the one we use is a little easier to understand and work through.
The one we use creates a reusable pattern that can be used for other things, not just the birth date.
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.
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.
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.
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”
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.
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.
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.
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.
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!
Sorting by months is a common issue that I am asked about on a normal basis. What the issue is, is there is a date table that is being used that lists months in a visualization. Whether it’s a bar chart, table, line chart, etc., the months column always shows up in alphabetical order (April, August, December, February…) rather than chronologically (January, February, March, April…). Below is a fix for this exact issue.
Problem: Months are sorting alphabetically instead of chronologically within my visualizations. Example: Below I have two tables that consist of data from a mock company that I use for demonstrations that contain A)Month/Sales amount & B)Month/Year/Sales Amount
A) B)
As you can see, both representations of the same data are sorting by the column “Month Name” alphabetically. We want the “Month Name” column to display chronologically.
Solution: Using DAX, we need to define what the ordering we want is exactly andthen we can sort by the definition we created.
You may already have your own Date table to use, but for an example, let’s start off by building a date table to represent what we want.
To start off making our “Date” table for demonstration purposes, we first create a new calculated table (using the “New Table” button in the Modeling tab) and create our first column with the following DAX expression using the CALENDAR function:
Date = CALENDAR("1/1/2000", "12/31/2018")
Next, we are going to create our next Calculated Column, which uses the MONTH function, to extrapolate the month number from the [Date] column that we previously created. This is the column that defines the ordering of when each month appears in a calendar, chronologically. This is done by selecting the “New Column” button in the Modeling tab and writing the following DAX expression:
Month = MONTH([Date])
Now we will create another Calculated Column that will extrapolate the month and convert it to the actual month name, using the FORMAT function, with the following DAX expression:
Month Name = FORMAT([Date],"MMMM")
The last column we may want to create is one that just displayed the year. In our case, we’ve created a table that only displays dates between the years 2000 & 2018. To do this, we can extrapolate the Year from the [Date] column using the YEAR function in the following DAX expression:
Year = YEAR([Date])
Since we are finished creating the necessary Date table, I went ahead and imported my demonstration sales data into Power BI Desktop as a source.
Here are the aforementioned examples, Table A) and Table B), showing the month.
A) B)
In the Date table that we created, we defined the order of months with the [Month] column, now how do we sort by that column?
We do so by selecting the [Month Name] column, and selecting the “Sort By Column” button that is located in the Modeling tab and choosing “Month” (this is referring to the [Month] column that has each of our months assigned numbers 1-12).
Now, within the visualized table, select “Month Name” so it changes the sort order by “Month Name”, and you will see that the months are now listed in chronological order!
![Example_thumb[2]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/example_thumb2.png "Example_thumb[2]")
![Example 2_thumb[3]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/example-2_thumb3.png "Example 2_thumb[3]")
![date_thumb[2]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/date_thumb2.png "date_thumb[2]")
![month number_thumb[2]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/month-number_thumb2.png "month number_thumb[2]")
![month name_thumb[1]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/month-name_thumb1.png "month name_thumb[1]")
![year_thumb[1]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/year_thumb1.png "year_thumb[1]")
![Example_thumb[2]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/example_thumb21.png "Example_thumb[2]")
![Example 2_thumb[3]](https://backtosql.wordpress.com/wp-content/uploads/2018/03/example-2_thumb31.png "Example 2_thumb[3]")
backtosql 