PowerQuery: Inserting New Columns based on certain criteria

[Jose Fonyat]

Dear members, let me know if you can help me with this problem:

My table is the following:

Customer	IdCustomer	Start	End	N.Days
John	1	15/12/2017	18/12/2017	3
Bruce	2	10/01/2017	12/01/2017	2

My Goal is to achieve this new table:

The idea is:
- Create as many rows as the N.Days and repeat all content
- Create a new column and insert the correspondent date of use (DayUse)

Name	IdCustomer	Start	End	N.Days	DayUse
John	1	15/12/2017	18/12/2017	3	15/12/2017
John	1	15/12/2017	18/12/2017	3	16/12/2017
John	1	15/12/2017	18/12/2017	3	17/12/2017
Bruce	2	10/01/2017	12/01/2017	2	10/01/2017
Bruce	2	10/01/2017	12/01/2017	2	11/01/2017

Thanks a lot
Best Regards

 

[MarcelBeug]

First, add a column with formula ={1..[N.Days]}
You may use the default column name "Custom".
Next, expand this column (2-arrows-button in the column header).
Next, add a column "DayUse" with formula = Date.AddDays([Start],[Custom]-1).
Finally, remove column "Custom".

 

[Jose Fonyat]

Thanks a Lot Marcel!

Best Regards!!!

 

[p45cal]

I realise this is an old thread; I'm using them as practice to learn.

I'm guessing that in the 2.5 years since Marcel posted his solution List.Dates has been made available, which can reduce the number of steps:
Instead of:
First, add a column with formula ={1..[N.Days]}
try starting by adding a column named DayUse:
= List.Dates([Start],[N.Days],#duration(1,0,0,0))
and expanding that. No columns to remove.