Hi @svandamme,
It is possible. Please find the steps I processed in Power query ( you can find elegant ones).
Please change the Source or add the steps after the Source.
To sum up:
- I created Datediff :the difference between the date from and the date still ( transform the data to Whole number)
- Create a repeated list based on the Datediff
- Expand the table
-Remove unnecesseray columns
-Regroup and created index
-Add date with the index created
-Remove unnecessary columns
Hope it helps
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ExchangeRateValidDateFrom", type datetime}, {"ExchangeRateValidDateTill", type datetime}, {"ExchangeRate", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Datediff", each [ExchangeRateValidDateTill]-[ExchangeRateValidDateFrom]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Datediff", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each List.Repeat({[Datediff]},[Datediff])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Datediff", "Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"ExchangeRateValidDateTill"}, {{"Count", each Table.AddIndexColumn(_, "Index1", 0, 1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"ExchangeRateValidDateFrom", "ExchangeRate", "Index1"}, {"Count.ExchangeRateValidDateFrom", "Count.ExchangeRate", "Count.Index1"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Count", "Date", each Date.AddDays([Count.ExchangeRateValidDateFrom],[Count.Index1])),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"ExchangeRateValidDateTill", "Count.ExchangeRateValidDateFrom", "Date", "Count.ExchangeRate", "Count.Index1"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Count.Index1"})
in
#"Removed Columns1"Ninter
