Steve Wise

- Creating Power BI solutions for customers including mashups, modeling, and visuals. - Conducting customer discovery meetings to determine reporting requirements. - Working with data source team members to support performance improvements and data quality. - Guiding customers on deploying Power BI solutions to their enterprise Power BI environment.

Daylight Savings Time Changes in Power Query

In this post, I’ll show how to accommodate for Daylight Savings Time changes in a Power BI file. The video I’ve included will show the formulas that I use and how they adapted from Daylight Savings Time to Standard Time. There are many ways to do this and it is best to find a way that accommodates what you need to do, but I’ll walk through how I do it for my needs within Power Query.

  • The first thing I do in Power Query is to identify the start and end date of Daylight Savings Time. In the US, the start date is the second Sunday in March and end date was the first Sunday in November and I’ve added the time of 2:00am.
  • You could look these up and make a list of them for the next 8 or 10 years but to do it formulaically, I use a DateTime.LocalNow and Date.Year to pull the year out of whatever year we are in.
  • In the Applied Steps area in my query settings, I create a small temporary calendar table using that the year that will start in March and end in November. I create a list of those dates under source and then I convert that list into the actual dates.
  • No I have a calendar table with column1 being all dates listed from the start of March thru November. Using the Added Custom fields in Applied Steps, I add a column for month number and one for number of the day of the week (0-6).
  • Since I’m only interested in Sundays in March and November, I filter this table to show me only Sundays (day of week 0) in those months. More specifically, I need to know the second Sunday in March and the first Sunday in November. To do that, I want to group rows by their month number and then add an index column.
  • If I click on one of the columns in my table, I can see a Sunday index which is counting in order (1st, 2nd, 3rd Sunday). If I remove the other columns and just keep the one with the index and expand it, I see I have all the information pieces I need (month number, Sunday index columns).
  • Next, I’ll add a little helper column (GetDays) that will combine the month number and Sunday index columns together. I filter that helper column for the 2nd Sunday in March and the 1st Sunday in November.
  • So, I have all I need but I just need to add a time, so I’ll put a time component in and combine it with the date, so I have a DateTime column.
  • It’s important to note that when we do comparisons in Power Query between dates, date times or date time zone, you want to be consistent with your format. If I’m going to compare date time zones, I need to put my date times into a date time zone. I also need to ensure that the time zone is the same, so I’m going to use a consistent switch. I’ll add a time zone to my start and end dates.
  • I’ve added two queries (StartTime and EndTime) in which I’ll reference that DateTime list. In my formulas, I’ll write in the DateTime in March as the min and the end DateTime in November as the max. I’ll also need to add the time zone to each and make sure I’m consistent. Check out my video for more detail of the formulas.
  • The logic here is an If/Then statement. If the switch time zone which I’ve set to -4 (which is Eastern Time during Daylight Savings), is greater than the start time and less than the end time, then we are in Daylight Savings and the time is correct.
  • If it’s not in between those two dates, then we’re not in Daylight Savings and we need to subtract an hour from it because now we’re going to fall back. In other words, we’re going to be 5 hours behind instead of four. If I refresh it again, it should match the time that’s on my computer. In my SwitchTime Zone column, I see the time that it was in the summer and in my DateTime with DST Correction column I see the correct time now which matches my computer, so I know that this is working correctly.
  • But how do I know it’s working to the exact second? I’ll just duplicate my three queries and create a testing query. In this testing query I will arbitrarily set the start and end time to be a specific day and time to check back on my table to see if it matches. This testing helps ensure that it’s working perfectly.

My video below goes into more detail and may make this written demo clearer. I hope my peek into how I can accommodate for Daylight Savings within Power Query is helpful. This is certainly not the only way to do this, but it works well for me, so I thought it was worth sharing.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected]

Steve WiseDaylight Savings Time Changes in Power Query
Read More

Avoiding Issues When Generating Time/Date in Power BI

Have you ever run into hurdles when trying to generate time and date in Power BI? I know I have, so I’m here to help and to demonstrate the differences between generating time and date in Power BI Desktop vs in the Power BI Service.

A few key points before I dig in:

When we generate time and date in Power BI Desktop using the native Power BI tools, it pulls that information from our local computer settings.

In the Power BI Service, the Service generally exists in a different server time and date than your local machine. Therefore, there will be some differences between the times as they appear in the desktop file and how they appear in the Service.

Currently, there is no native functionality to handle Daylight Savings Time changes. Keep this in mind when you start to generate your own time and dates – you’ll need to create tools that take this into account.

What I’ve done will be much easier to see by watching my video instead of trying to explain in text, as it could be confusing. Here’s a brief overview of my video.

  • In my video, you’ll see that I went into Power Query and copied the different queries that I used to generate time and date.
  • The first one is generating time and date in Power Query using DateTImeZone.LocalNow to pull the local time. Remember, when operating in the PBI Desktop, this is pulling information from my local machine.
  • What I’m going to do is reuse some of the formulas and look at how we can switch the time and what those changes look like as I go through the queries I’ve highlighted.
  • In my demo, you’ll see that rather than publishing this file and jumping in the Power BI Service to look at it, I’ve taken the queries that I’ve used to generate my times, put them in the Service as a data flow, and then imported that dataflow into a Power BI Desktop file.
  • So, I have 2 pages; one shows the information from the data flow and the other is the information generated locally. This way I can show side by side what the changes are going to look like.
  • I’ll walk you through the steps of these scenarios and the queries I used:
    • Manually switching time using #duration
    • Modifying date/time to reflect Eastern Standard Time (-4 UTC) using SwitchZone
    • Modifying time/date in Power Query using SwitchZone and FixedLocalNow (time at start of query execution)
    • Adjusting time -4 or -5 based on daylight saving calendar

In summary, this post covers the changes to date/time as we move from Power BI Desktop to the Service and what are the best formulas to use when we are generating our own time. You’ll see that the key takeaway is the best way is to use SwitchZone when adjusting between the Desktop and the Service.

I think you’ll find this helpful when running into date/time hurdles in Power BI.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Steve WiseAvoiding Issues When Generating Time/Date in Power BI
Read More

Testing Row-Level Security in the Power BI Service

In my last post, I showed how to test row-level security in Power BI Desktop. In this follow up, you’ll learn how to test row-level security in the Power BI Service once you’ve published your file there. Also, please check out my demo at the end of this post.

  • After navigating to the workspace where I published my file, I need to find the data set and select Security from the option menu, as row-level security resides at the data set level.
  • On the next screen, you should see the role that was created in Power BI Desktop, along with a field to enter your role membership, in other words, people or groups who belong to this role.
  • You will see the functionality to test row-level security only when you hover the mouse over the role and get the ellipsis. Click on this ellipsis and you’ll get the option that says, ‘test as role’.
  • This will take us to the report page where it appears that row-level security is immediately applied. I know this because I see that Power BI is taking my login credentials and applying it to the report which matches what I saw when I was testing this in Power BI Desktop.
  • If I want to change the user that I’m testing, I can enter a new email by clicking on the menu at the top.
  • One important thing to clarify:
    • Once a PBI file is published to the service, there are other considerations that can impact if row-level security works as expected, such as workspace membership roles, data set permissions and data set security role memberships.
    • For example, if we look at our workspace membership, we see that we have 3 report users with 3 different roles. Row-level security only applies to users in a viewer role, not to members, admins, or contributors.
    • In my example, I am admin of the report, so if I return to the report page and type in my email, I have access everything and the actual result is returned. This also applies to contributors and if I type in their email, they will get the same result with access to all the data, just as I did as an admin.
    • If I type in the email of a viewer, I expect to see his information filtered on the page, but instead a get a bunch of errors. This is because row-level security applies to viewers since they do not have a role membership, so Power BI won’t let them see the data.
    • To give a viewer access, you’ll need to go back to the row-level security page and with the role selected, we can enter the viewer’s email in the Members field, then click add and save.
    • A best practice here is to only use security groups when you’re creating role membership; it’s easier than explicitly entering names and is much easier to maintain.
    • To test if this worked, I simply click on test this role, go back to my report page and enter the name of the viewer whom I just added, and the page should filter to what the view can now see.
  • What happens when role testing doesn’t work correctly? Check out my graphic below which gives 3 examples of errors returned and the reasons why role testing may not work.

I hope this post on testing row-level security in the Power BI Service was helpful. If you have questions or want to know more about row-level security in Power BI, about Power BI in general or any Azure product or service, let us know – our team of experts are here to help.


Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

 

Steve WiseTesting Row-Level Security in the Power BI Service
Read More

How to Open Your Power BI Report in the Embedded Playground

Are you familiar with the Power BI Embedded Playground? First, for those of you who don’t know, Power BI Embedded is a Microsoft Azure service that allows developers to embed visuals, reports, and dashboards into an application. The Embedded Playground is an interactive showcase created for developers to explore embedded features and capabilities for their applications.

Steve WiseHow to Open Your Power BI Report in the Embedded Playground
Read More

New Power BI Deployment Pipelines Feature

Microsoft recently announced a public preview of Power BI deployment pipelines which is a new and improved way for BI teams to manage content lifecycle within the Power BI Service. This feature is only available to workspaces in premium capacity.

Steve WiseNew Power BI Deployment Pipelines Feature
Read More

Dynamic Row Level Security in Power BI

Interested in learning how to set up row level security (RLS) in Power BI when you have varied criteria? In this post I’ll walk through a scenario of setting up dynamic row level security in which your users have unique access needs. In other words, one user has access to the Northeast and Midwest regions, another has access to the Southeast, and two others overlap with access to the South and Northwest regions.

Steve WiseDynamic Row Level Security in Power BI
Read More