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

2) creates the date column, where the first date is [From] and the last is [To] - and between these dates there are consecutive days

3) each unique combination of [product + price + from + to] appends to each other - oucome is the desired table.

 

Do you guys think it is possible to perform that transformation in power query?

 

 

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"

2 Replies

  • 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"