Forum Discussion

matherhorn64's avatar
matherhorn64
Frequent Visitor
9 years ago
Solved

Table transformation - create rows for dates between - power query

Hi guys,   Current situation - I have this input table:   Desired situation -    So the idea is that power query transforms the input table: 1) gets the product from product column...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    It is quite easy if you convert the dates to numbers, add a column with nested lists with all numbers representing the from..to dates,

    remove the From/To columns, expand the new column and adjust the data type to date.

     

    let
    Source = InputTable,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", Int64.Type}, {"To", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {[From]..[To]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "To"}),
    #"Expanded Date" = Table.ExpandListColumn(#"Removed Columns", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
    #"Changed Type1"
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

It is quite easy if you convert the dates to numbers, add a column with nested lists with all numbers representing the from..to dates,

remove the From/To columns, expand the new column and adjust the data type to date.

 

let
    Source = InputTable,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", Int64.Type}, {"To", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {[From]..[To]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "To"}),
    #"Expanded Date" = Table.ExpandListColumn(#"Removed Columns", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
    #"Changed Type1"
  • matherhorn64's avatar
    matherhorn64
    Frequent Visitor
    9 years ago

    Brilliant, thanks kind sir!