Power BI: Calculating Network Days in M Custom Columns

Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance indicators). While getting the days between two dates is fairly easy to achieve, they tend to follow up and ask how many week days there are between two timed events. For example, one company may have a SLA to ship an order within three week days of the order being placed or else a discount is applied to the order. In this case, I would highly recommend that the company have software that calculates these days in the background and stores the actual week days between order date and ship date in a database. Unfortunately, many companies create policies like this without considering future reporting needs and these values have to be calculated on the backend.

Please note, this blog will not cover how to calculate business days (week days that are non-holidays) because that should be done upstream from Power BI in either an application or SQL stored proc that can get/set holidays dynamically (the date of Easter changes every year). International clients often have different holidays which can infinitely complicate the definition of a business day. Also, many coding languages have built in functions to get business days while Power BI does not (sadly, the Excel NETWORKDAYS() function is not currently in Power BI) . So for this, we will stick to excluding weekends and getting a count of the week days between two events.

The Final Equation

To make it easier to read, SD means start date and ED means end date. SWD will mean the work week day of the start date and EWD means work week day of the end date. For example, if the order date was May 2nd 2022 and the ship date was May 4th 2022, then the SD = 5/2/2022, SWD = Monday/day 2 of the week, ED = 5/4/2022, and EWD = Wednesday/ day 4 of the week. But let’s say the SD was May 7th which is a Saturday. We would adjust this to be Friday/ day 6 of the week. I’ll put code below to show how to adjust the weekends to Friday before and how to adjust to the Monday after, but the GitHub file will align weekends to the Friday before.

Week days between = ( if SWD > EWD then (5 + EWD – SWD) else (EWD – SWD)) + (5 * ( Actual Days Between /7 rounded down))

In plain English: the week days between two dates starts by looking at if the start date is earlier in the week than the end date. If the start date is later in the week than the end date, then add five to the difference between their day of the week (for example, end day of Monday to start day of Wednesday would be 5 + 4-2). If the start date is earlier in the week than the end date, then take the difference between those two days of the week (start day Monday to end day Wednesday would be 4-2).

Next, take the actual days between your end and start date including weekends, divide that by seven then round down. That will give you the number of full weeks between your two days. Multiple that number by 5 to represent the five days within each of those weeks.

Finally, add those two numbers together to get your week days between the start and end dates.

Building the Variables

To create that final equation, we will need to build a few extra columns in the Power Query Editor. For each one, we will click on “Transform Data”, go to the “Add Column” tab, then select “Custom Column”. This will open a window to type in some custom M code (yay another coding language!).

Don’t worry though, I have ya covered with the code you need for this demo. To start, identify your columns with your start date and end date. For this demo, we will have Start Date = Order Date and End Date = Ship Date. The code below will use StartDate and EndDate for these fields, but the GitHub file will use Order Date and Ship Date.

The code below will turn Saturday and Sunday into Friday:

Start Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 1 
then Date.AddDays([StartDate],-2) else
if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 7 
then Date.AddDays([StartDate],-1) else [StartDate])
,Day.Sunday) +1

End Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 1 
then Date.AddDays([EndDate],-2) else
if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 7 
then Date.AddDays([EndDate],-1) else [EndDate])
,Day.Sunday) +1

The code below will turn Saturday and Sunday into Monday

Start Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 1 
then Date.AddDays([StartDate],1) else
if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 7 
then Date.AddDays([StartDate],2) else [StartDate])
,Day.Sunday) +1

End Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 1 
then Date.AddDays([EndDate],1) else
if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 7 
then Date.AddDays([EndDate],2) else [EndDate])
,Day.Sunday) +1

Here’s the formula with some comments added in:

Work Weekday = 
Date.DayOfWeek( //sets us up to get the day of the week at the end of the formula
(if (Date.DayOfWeek([Date],Day.Sunday)+1) = 1 //if it's Sunday 
then Date.AddDays([Date],-2) else //then go back two days to get Friday's date
if (Date.DayOfWeek([Date],Day.Sunday)+1) = 7 //if it's Saturday
then Date.AddDays([Date],-1) else [Date]) //then go back one day to get Thursday's date, otherwise give me the date 
,Day.Sunday) +1 //now give me the day of the week for the date left from the previous if statements

Alrighty, the next building variable we will need for our equation is the actual days (including weekends).

Days Between = Duration.Days([EndDate]-[StartDate])

In our case though, there is a possibility that the orders have not shipped. To handle any errors where there is no EndDate or StartDate, use the equation below:

Days Between = if [EndDate] is null then null else
if [StartDate] is null then null else
Duration.Days([EndDate]-[StartDate])

Okay great, now we can put it all together using our new fields!

Weekdays Between = 
( if [Start Date Work Weekday] > [End Date Work Weekday] 
then ( 5 + [End Date Work Weekday] - [Start Date Work Weekday] )
 else ( [End Date Work Weekday] - [Start Date Work Weekday] )) 
+ ( 5 * ( Number.RoundDown ( [Days Between] / 7 )))

Boom all done! You made it through a lot of complicated M code, nice work! Below is the M used in the GitHub file so you can see how it will look in the advanced editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\KristynaHughes\OneDrive - DataOnWheels\GitHub\AdventureWorks Sales.xlsx"), null, true),
    Sales_data_Sheet = Source{[Item="Sales_data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sales_data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SalesOrderLineKey", Int64.Type}, {"ResellerKey", Int64.Type}, {"CustomerKey", Int64.Type}, {"ProductKey", Int64.Type}, {"OrderDateKey", Int64.Type}, {"DueDateKey", Int64.Type}, {"ShipDateKey", Int64.Type}, {"SalesTerritoryKey", Int64.Type}, {"Order Quantity", Int64.Type}, {"Unit Price", type number}, {"Extended Amount", type number}, {"Unit Price Discount Pct", Int64.Type}, {"Product Standard Cost", type number}, {"Total Product Cost", type number}, {"Sales Amount", type number}}),
    #"Added Order Date" = Table.AddColumn(#"Changed Type", "Order Date", each Date.From( Text.Middle(Text.From([OrderDateKey]),4,2)&"/"& Text.End(Text.From([OrderDateKey]),2)&"/"& Text.Start(Text.From([OrderDateKey]),4)),type date),
    #"Added Ship Date" = Table.AddColumn(#"Added Order Date", "Ship Date", each Date.From( Text.Middle(Text.From([ShipDateKey]),4,2)&"/"& Text.End(Text.From([ShipDateKey]),2)&"/"& Text.Start(Text.From([ShipDateKey]),4)), type date),
    #"Added Order Weekday" = Table.AddColumn(#"Added Ship Date", "Order Date Weekday", each Date.DayOfWeek(
(if (Date.DayOfWeek([Order Date],Day.Sunday)+1) = 1 
then Date.AddDays([Order Date],-2) else
if (Date.DayOfWeek([Order Date],Day.Sunday)+1) = 7 
then Date.AddDays([Order Date],-1) else [Order Date])
,Day.Sunday) +1,Int64.Type),
    #"Added Ship Weekday" = Table.AddColumn(#"Added Order Weekday", "Ship Date Weekday", each Date.DayOfWeek(
(if (Date.DayOfWeek([Ship Date],Day.Sunday)+1) = 1 
then Date.AddDays([Ship Date],-2) else
if (Date.DayOfWeek([Ship Date],Day.Sunday)+1) = 7 
then Date.AddDays([Ship Date],-1) else [Ship Date])
,Day.Sunday) +1,Int64.Type),
    #"Added Days Between" = Table.AddColumn(#"Added Ship Weekday", "Days Between Order and Ship", each if [Ship Date] is null then null else if [Order Date] is null then null else Duration.Days([Ship Date] - [Order Date]),Int64.Type),
    #"Added Weekdays Between" = Table.AddColumn(#"Added Days Between", "Weekdays From Order to Ship Date", each if [Days Between Order and Ship] is null then null else
( if [Order Date Weekday] > [Ship Date Weekday] then (5 + [Ship Date Weekday] - [Order Date Weekday] ) else ( [Ship Date Weekday] - [Order Date Weekday] )) + (5 * (Number.RoundDown([Days Between Order and Ship]/7))),Int64.Type)
in
    #"Added Weekdays Between"

Additional Resources

Connecting to Azure Blobs in Power BI

The step-by-step process below walks through connecting to data housed in Azure Blob Storage from Power BI using a SAS token. There are many ways to grab your data from Blob Storage, but this is the most efficient, scalable, and secure way that I found (with some security restrictions from watchful DBAs).

Resources Needed:

  • Base URL for container
  • SAS Token (must have read AND list permissions)
    • Check out the link in resources for a tutorial on generating SAS Tokens.
  • File Path (should end with .csv)
  • Power BI Desktop

Notes:

  • You can skip ahead to the sample M script if you have all your elements. Simply swap out the BaseURL, SASToken, and FilePath and you’re good to go. Otherwise, feel free to walk through the steps below to gain a deeper understanding of the process.
  • Make sure your Base URL ends with a “/”, your SAS Token starts with “?”, and your file path ends with “.csv”
  • Keep the double quotes around each parameter value, this forces Power BI to recognize it as text.

Process:

  1. In Power BI Desktop, go to Get Data and select the Web option.
  2. Switch to the advanced view and put the base URL in the first box.
  3. Put in the second box the SAS token.
  4. In a third box (click add part to get the third one), put “&restype=container&comp=list” (this will allow you to list all the blobs in that container).
  5. Expand the blob down then filter the name on the file path.
  6. Create a custom column to create the entire URL for the file (M code samples are below).
    • FileURL = BaseURL & [Name] & SASToken
  7. Create another custom column to access the web contents of your FileURL column.
    • BinaryURLContents = Web.Contents([FileURL])
  8. Remove all columns except the BinaryURLContents.
  9. Click on the “Binary” value and watch Power BI expand out your CSV file.
  10. Manipulate data from there as needed.

Final M Code:

let
    BaseURL = "BASE_URL_HERE"
    ,SASToken = "SAS_TOKEN_HERE"
    ,FilePath = "FILE_NAME_HERE_(Note do not include section of the URL from Base URL)"
    ,Source = Xml.Tables(Web.Contents(Text.From(BaseURL) &Text.From(SASToken) & "&restype=container&comp=list")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Blobs"}),
    #"Expanded Blobs" = Table.ExpandTableColumn(#"Removed Other Columns", "Blobs", {"Blob"}, {"Blob"}),
    #"Expanded Blob" = Table.ExpandTableColumn(#"Expanded Blobs", "Blob", {"Name", "Properties", "OrMetadata"}, {"Name", "Properties", "OrMetadata"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Blob", each ([Name] = Text.From(FilePath))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "FileURL", each BaseURL &  [Name] &  SASToken),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "BinaryURLContents", each Web.Contents([FileURL])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"BinaryURLContents"}),
    BinaryURLContents = #"Removed Other Columns1"{0}[BinaryURLContents],
    #"Imported CSV" = Csv.Document(BinaryURLContents,[Delimiter=",", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
  in
   #"Promoted Headers"
//Use this query to validate your file path
let
    Source = Xml.Tables(Web.Contents("BASE URL" & "SAS TOKEN" & "&restype=container&comp=list")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NextMarker", type text}, {"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Blobs"}),
    #"Expanded Blobs" = Table.ExpandTableColumn(#"Removed Other Columns", "Blobs", {"Blob"}, {"Blob"}),
    #"Expanded Blob" = Table.ExpandTableColumn(#"Expanded Blobs", "Blob", {"Name", "Properties", "OrMetadata"}, {"Name", "Properties", "OrMetadata"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Blob", each [Name] = "FILE PATH")
in
    #"Filtered Rows"

Additional Resources:

Power BI: Making Date & Time Keys

Saving the Day from Delay Part 2

Creating DateKey and TimeKey columns can be done with built in functions in the Power Query editor. Quick call out, if you need the time along with dates, I highly recommend splitting your datetime columns in half – one date only and one time only. From there, you can use the same process to convert your time to a decimal number and use a Time Table for your time functions (GitHub link below). Below are some screenshots to walk you through the process.

Let’s say you have a datetime column like my Date column below. To start, I recommend going to the Add Column tab in the query editor, and select Date Only then Time Only to create two new columns. This way the new columns will be right next to each other in the applied steps which will make troubleshooting down the road a lot easier. Don’t forget, you can right click on steps and rename them to help yourself walk through and/or adjust steps in the future.

Time to make our keys! There are a couple ways to do this, but the easiest is to click on the calendar icon (or clock icon for time) and select whole number (select decimal for the time only column). If you’ve worked in Excel, this will look familiar. These whole numbers for date (or decimal for datetime) is the same across the two platforms and is what DAX uses in the background to process datetime equations.

Final Date and DateKey Columns
Final Time and TimeKey Columns

And that’s it! Next post we will look at how to join the date and time tables to your keys in the data model.

External Resources:
https://data-mozart.com/inside-vertipaq-compress-for-success/
https://github.com/AnytsirkGTZ/TimeTable_MCode/blob/main/MQuery%20Time

Power BI: Data Model Optimization

Saving the Day from Delay Part 1

Optimizing your data model can be a daunting task. If you read the intro to this series, you know one of the most efficient and sustainable solutions to a bogged-down data model is to remove native date queries and use a date table. This post will dig into how and why this will speed up performance in both refreshes and in the online PBI service and how to make date and time keys.

The key to optimization is compression. An efficiently compressed data model is a lean, mean, query running machine. There are two types of compression – horizontal and vertical. Horizontal compression occurs on a row by row basis while vertical compression occurs column by column. Power BI uses the Vertipaq Engine, a vertical compression model, to compress data inside the data model. While vertical is more CPU intensive, it is also more efficient as it finds the best option for compression based on the data type in the column (values/whole numbers are most efficient). Data mozart does an in-depth look on this process that I highly recommend reviewing if you have more questions (link at bottom of this post).

Vertical compression is significantly slower on date time columns than value columns. DateKeys are your best friend in compressing your data model because they allow you to capture vital date information but store it in a value format (the most optimal format – think whole number). Converting all your primary date fields to a DateKey will allow all calculations using that primary date field to run much faster as Vertipaq can process the requests more efficiently.

Next post we’ll cover making time and date keys in the Power Query Editor.

External Resources:
https://data-mozart.com/inside-vertipaq-compress-for-success/
https://github.com/AnytsirkGTZ/TimeTable_MCode/blob/main/MQuery%20Time

Saving the Day from Delay: Recap & Series Kick-Off

Ironically, I’m a few months behind on a recap for my presentation on building and using a sustainable, dynamic date query in M code – Saving the Day from Delay. On a positive note, the delay means that I can do a series on the importance of date tables, how to effectively use them in DAX, and what I mean by sustainable and dynamic practices. At the end of this post is a link to a GitHub containing my most up-to-date M Query referenced in this series and the PowerPoint used in the aforementioned presentations.

Presentation Recap

Using a date query, whether from a SQL table or Power Query table (M), can aid in optimizing and honing your data model. A main culprit in sluggish Power BI data models are the native date hierarchies. Native date hierarchies are the default setting in Power BI and provide you with hierarchies to use in visuals that allow for easy drilldowns through year, quarter, month, and day. You can turn off this default under File > Options > Current File > Data Load (see below). You can also do this in global settings, but I recommend doing this file by file as the benefits reach diminishing returns on datasets containing under a 500,000 rows.

Where to change your default settings

If you’re unsure what I mean by native data hierarchy, the image below contains an example of a date hierarchy built by Power BI. To rebuild a date hierarchy from a standard date table, drag and drop columns on top of each other to create a custom date hierarchy. By right clicking on the Hierarchy title, you can rename and reorder your hierarchy accordingly. One of the biggest reasons to turn off the native date hierarchy is that you now only have one hidden DAX table in your data model for the date instead of however many date fields you have in your model (I’ve seen some models with up to 15 date fields!).

Native Date Hierarchy

In subsequent posts, I’ll cover the benefits of turning off this feature, reconnecting your data model, how to customize the date query, custom columns in M, and best practices for a lean, mean data model machine.

https://github.com/AnytsirkGTZ/DateTable_MCode/blob/main/MQuery%20Calendar