Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Power Query Power BI | Create New Records Based On Date Fields

by | 7:00 pm EDT | October 21, 2021 | Power BI

For this tutorial, I want to showcase a couple of methods on how you can create new records based on date fields using Power Query Power BI. You can watch the full video of this tutorial at the bottom of this blog.

I’m going to showcase two methods on how to achieve this. This example came from a member of the Enterprise DNA forum, who posted a question with a date and a number of nights stayed. The member wanted to know how to create new records for each night a person stayed at a motel using Power Query.

## Method 1: Using Number.From Function In Power Query Power BI

If you’re familiar with date calculations in Excel, you’ll probably know that each day can be translated to a whole number, and you can do that here as well to get the numeric equivalent for each date. We can use the Number.From function.

I’ll reference my source query by right-clicking on it and select Reference. Then via that mini table, go Add Custom Column. Now let’s enter the function Number.From and point to the first actual column that contains our dates. Don’t forget the closing parenthesis and press OK.

So here we see an integer representation for the date value. And because it’s a number, we can add and subtract days. The goal is to create a date for each consecutive night stayed.

If we examine the first record, that person stayed for just a single night, so we only have to return that first actual date value. Then for the second record, that person stayed for two consecutive nights. We have to return both a value for the 13th and the 14th of December. We can achieve that if we add the value that’s in the Nights column to the value that we have in our Custom column, and then subtract one because we already passed that initial value.

So, let’s go to the formula bar and expand on the logic we’ve created so far. We have to use that numeric value for the date twice, so I’ll store that in a variable. I’ll add the let expression, then add a name for my variable. I’ll call that FirstNight. And then, we can add the in clause.

Lists can be expanded to new rows with a set of list initializers, which are the curly brackets. We can create a list with dates. We’re going to start from our FirstNight variable up to the FirstNight variable, plus whatever is in our number of nights. With a set of square brackets, point to the Nights column, and then subtract one. I’ll press OK, and we now have a list.

When I click off to the side in the white space, we can see the contents of that nested list down below in the second preview pane.

Now with these sideward arrows, we can Expand to New Rows. Then, I can set the data type into a date. So you can see that we now have the dates. For the first record, we’re returning that same value that’s in the first actual. For that second record, we have one record for the 13th and one for the 14th of December.

## Method 2: Using M Function List.Dates In Power Query Power BI

For the second method, we can use an M function called List.Dates. I’ll reference my source query, and again, with that mini table icon, I’ll add a custom column.

I’ll add that function List.Date and then put it in an opening parenthesis. First, it wants a start value as a date and that’s in our First Actual column that contains our date values. So, we select that then put a comma. Next, is a count as a number, which means how many dates you want to return to that list. And we can see that that is in the Nights column.

Next, is step as duration. Think of that as an interval. Now we’re looking for consecutive nights and we want a continuous list of dates. A single day has a numeric equivalent of one, and so I’ll enter duration from one, then a closing parenthesis and press OK.

Again, we have a nested list. For the first record, we have just one value and for the second record, we have the 13th and the 14th of December.

Next, we can again Expand that to New Rows and set the appropriate data type to date.

Now, I want to point out the first perimeter in the List.Dates function requires a date data type. If I step to my Source step and change this data type into a Date/Time, it returns an error. That’s because it’s not passing a date. We can easily fix that by going into the formula bar and adding Date.From.

Another thing that I want to point out is that you can also create an alternating list of dates. All you need to do is change that final step parameter into a 2, for example.

## Conclusion

In this blog, I’ve demonstrated two ways to create new records based on date fields. The first is by generating a list of dates using the list initializers and the second is by using the function List.Dates.

I hope you find this tutorial helpful. You can watch the full video tutorial down below and check out the links below for more related content.

All the best!

Melissa

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...