Unpivot based on value in a colum (e.g. duration)

[MLExcel]

Hi all,

I have a source table containing a start date column and a duration column (as well as other data columns):

ID	start date	duration	other data1
1	1/Jan/16	2	abc
2	15/Feb/16	4	xyz
...	...	...	...

<tbody>
</tbody>

Now I would like to change the data to:

ID	date	other data1
1	1/Jan/16	abc
1	2/Jan/16	abc
2	15/Feb/16	xyz
2	16/Feb/16	xyz
2	17/Feb/16	xyz
2	18/Feb/16	xyz
...	...	...

<tbody>
</tbody>

I am trying (i.e. I would like to) perform this in the Get and Transform editor.

Is this possible?

I am using:
Win10 Pro x64
Excel 2016 (MS Office 365 ProPlus)

Apologies in case this has been asked before and I was unable to find it.

Many thanks for any hints.

Kind regards,
MLE

 

[MarcelBeug]

From Excel table, be sure to adjust data type of the source date to date.
Otherwise the code was created via the UI, except for the reorder step.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start date", type date}, {"Duration", Int64.Type}, {"Other detail", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Start date],[Duration],#duration(1,0,0,0))),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"Start date", "Duration"}),
    #"Reorder Columns" = Table.ReorderColumns(#"Removed Columns",{"ID","Date","Other detail"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reorder Columns",{{"Date", type date}})
in
    #"Changed Type1"

 

[MLExcel]

Thank you very much.

The line:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Start date],[Duration],#duration(1,0,0,0)))

was actually all I was missing.
Lookup how it works here: https://msdn.microsoft.com/en-us/library/mt253613.aspx
and now I am fine.

Kind regards,
MLE