Refresh Date in Power BI

 "Today" seems like such a simple term, it should be clear what we mean when we say today - right? As I write this it's Jan 13, 2022 in Auckland, New Zealand, but still Jan 12, 2022 in most of Europe and North America. So when we publish reports to PowerBI.com, what date does it use for today?

Power BI Scheduled Refresh doesn't pull through today's data

Power BI uses UTC as its date value when determining "today". UTC does not change for daylight savings time, so New Zealand time zone is UTC + 12:00 (standard time) or UTC + 13:00 (daylight savings time). Having a universal standard time like UTC is handy, but only if we know how to use it and remember convert from our local time zones.

I've just had a client question why their Power BI report isn't pulling through the latest day's data. They were using a Date table with an end date of DateTime.LocalNow(), which works beautifully in Power BI desktop and uses the time on your local machine. However, in PowerBI.com Date.Time.LocalNow() returns UTC time.  

Power BI Desktop Refresh

In the screenshot below, you can see the result of doing a refresh at 11:14am on January 13, 2022 NZ time. This is 10:14pm on January 12, 2022 UTC time time:

Power BI Service (PowerBI.com) Refresh

In the screenshot below, you can see the result of doing a refresh at 11:13am on January 13, 2022 NZ time, but the DateTime.Local() function returns the wrong date - yesterday! DateTime.FixedLocalNow also returns the wrong date (at least for New Zealand). All functions return the UTC date and time, not the local time here in NZ like we'd want to see.

How to ensure Power BI refreshes at the correct date and time

Now that we know Power BI uses UTC time, how do we ensure it refreshes with the correct time? One option is to use the #duration() function in M code Power Query to add (or subtract) the number of hours from UTC:

=  DateTimeZone.UtcNow() + #duration (0,12,0,0)

Note the #duration() function accepts 4 arguments: days, hours, minutes, seconds. I have used 12 as the second argument to this function since I'm in New Zealand Standard Time right now and that is 12 hours after UTC.

If you're wanting to compare dates, just make sure that all your dates have the correct time zone information. If you're pulling data from a SQL database it will likely already have this info. If you're generating a DimDate table from M query you'll need to tell Power BI the timezone for those dates: 

= Table.AddColumn(#"Sorted Rows", "ShowDates", each if DateTime.AddZone( DateTime.From( [Date]) , 12 ) <=    DateTimeZone.UtcNow()   then 1 else 0)

The code above compares the [Date] column of my date table to the current time (in UTC). In order to make this comparison accurately, I must give Power BI additional information about which time zone I'm in. Thus I use the Date.Time.AddZone() function to add the 12 hours to get to NZ standard time. 

Get Data from SharePoint Files into Power BI

Today I'm finally going to let you in on one of my best kept secrets - how to get data from SharePoint files into Power BI without referencing your local C:\ (or D:\ or E:\) drive. This will ensure that you can schedule refreshes on the SharePoint files you are using with Power BI.

How to Get Data from Excel or CSV file on SharePoint into Power BI (without a gateway)

Step 1: Get SharePoint filepath URL

In order to schedule a refresh in PowerBI.com on files stored in SharePoint, you need to use the https:// URL. There are two main methods I use to grab this URL.

Method 1: From Teams or SharePoint

For this method to work, you must open the file in SharePoint. 

If you're starting in Teams:

• Click the 'Open in SharePoint' button on the ribbon of the Files tab:

• This will take you to the SharePoint document library for that file. Continue following the steps below for SharePoint.

If you're starting in SharePoint:

• Locate the file you want to import into Power BI.
• Click the three dots next to the file
• Select 'Details'
• A new pane will open on the right hand side of your screen - scroll ALL the way to the bottom
• Click the two sheets of paper next to the word 'Path' to copy the filepath
• That's it - you'll use this to get data into Power BI

Method 2: From Excel or desktop app

This method is easy if you've already got the file open in the desktop app. 

• Click File > Info
• Click the 'Copy path' button
• This URL will have a ?web=1 after it that you'll need to delete:
• https://excelwithallisonmvp.sharepoint.com/sites/Training/Resources/PowerBIEssentials/Adventure%20Works%20Product%20Categories.xlsx?web=1
• Keep only the main part of the URL:
• https://excelwithallisonmvp.sharepoint.com/sites/Training/Resources/PowerBIEssentials/Adventure%20Works%20Product%20Categories.xlsx
• That's it - that's what you'll need to get data into Power BI

Step 2: Get data into Power BI (use Get data from Web)

Even though your data source is likely to be an Excel or CSV file, we are going to use the 'Get data from Web' option to get the data into Power BI.

This is because we have an https URL that points directly to our file. The last part of the URL is the filetype (such as .xlsx) and Power BI will use that to automatically use the correct functions to import the data correctly.

• Click Get Data > Web
  
  
• Paste the URL from Step 1 (make sure you have deleted the ?web=1 if using Method 2)
  
  
  
• Click OK
• If you haven't used this data source previously, you'll be prompted to enter credentials. 
• Choose Organizational Account
• Sign in with the same account that has access to the SharePoint file.
  
  
  
  
  
• Click Connect
• That's it! 
• The next screen you see will depend on the file type. For an Excel file, I see a navigator window asking which sheets or tables I want to import. 
• Continue importing the data as you normally would for that filetype.
  
  
  
  
• When you publish this to PowerBI.com you'll be able to schedule a refresh using your current credentials - no Gateway needed!