0

looking for any advice/ tips that could help with the creation of this dummy data set

From this table, want to create another table

ID  MOB date(dd-mm-yyyy)
A01 2   01-07-2019
A02 3   01-02-2019

This is the required output: where the month is incremented each time with mob

ID  MOB date(dd-mm-yyyy)
A01 2   01-07-2019
A01 2   01-08-2019
A02 3   01-02-2019
A02 3   01-03-2019
A02 3   01-04-2019

I used Power query in excel, Add column> custom column > used this formula in the query editor

={1..[MOB]} 

this gave an output as :

ID  MOB date(dd-mm-yyyy)
A01 2   01-07-2019
A01 2   01-07-2019
A02 3   01-02-2019
A02 3   01-02-2019
A02 3   01-02-2019

the problem arose when closing the query editor and doing close and load to - it wouldn't load as it didn't support the data generated using {..}

Any more information on this query? or any other ideas to accomplish this as well as the date increment

Thanks in advance :)

2 Answers 2

1

When you expand the new column to rows, it effectively creates an index from 1 to MOB within each original date.

You just need to add to the original month that index-1 to get the incremented month.

enter image description here

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"MOB", Int64.Type}, {"date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[MOB]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "incremented_date", each Date.AddMonths([date],[Custom]-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"incremented_date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"incremented_date", "date"}})
in
    #"Renamed Columns"
1

You can use the Table.TransformRows method with List.Accumulate to create a list of the dates.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"MOB", Int64.Type}, {"date", type date}}),

//transform column 3 into a list of dates based on column2
    dtList= Table.FromRecords(
        Table.TransformRows(#"Changed Type",
                    (r)=>Record.TransformFields(
                        r, {"date",each //List.Dates(r[date],r[MOB],#duration(1,0,0,0))}))),
                            List.Accumulate({0..r[MOB]-1},{}, (state,current)=> state & {Date.AddMonths(r[date],current)} )}))),
  
  //expand the date list column
    #"Expanded date" = Table.ExpandListColumn(dtList, "date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded date",{{"ID", type text}, {"MOB", Int64.Type}, {"date", type date}})
in
    #"Changed Type1"

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .