Create New Records with Excel Power Query

Power Query
1

Hi

I am reading in a table that shows the First Date of a Motel Stay and how many consecutive nights they stayed. I want to create a record for each niht d stayed.

Please refer to attached.
Book3.pdf (15.0 KB)

How could i do this with Excel PQ?

Thanks

2

take a look at Chris Webb’s example here, I think he’s got exactly what you are looking for

3

Hi @AllisterB,

You most certainly can, so I took your first sample because that had multiple nights and added a Custom Column with this M code: List.Dates( [First Actual], [Nights], Duration.From(1))

image
image1012×629 22.3 KB

.
Now when you click of to the side (in the white space) you can see a preview of that List

image
image696×264 21.7 KB

Next all you have to do is click on the double arrow and select Expand to New Rows and you are done
image

.
I hope this was helpful.
eDNA - Create New Records.pbix (22.9 KB)

2 Likes
4

Thank You all - very useful