Add index based on a column

[Anonymous]

Hi 🙂

I need to create a table based on two columns on other table. 

Example: 

I have an activity with the duration in days 

Activity	duration days
1	2
2	5
3	1

I want to expand the days to repeat the activities

Activity	Day
1	1
1	2
2	3
2	4
2	5
2	6
2	7
3	8

[Anonymous]

Thank you AntrikshSharma!

I needed to do some small adjustments, and here we go! It works! 😄

Regards,

Vesta 

[AntrikshSharma · Accepted solution]

@Anonymous 

let
    Source = Excel.CurrentWorkbook(){[ Name = "Table1" ]}[Content],
    ChangedType = Table.TransformColumnTypes (
        Source,
        { { "Activity Duration", Int64.Type }, { "Days", Int64.Type } }
    ),
    RepeatDuration = Table.AddColumn (
        ChangedType,
        "Custom",
        each Table.FromColumns (
            { List.Repeat ( { [Activity Duration] }, [Days] ) },
            type table [ ActivityDuraton = Int64.Type ]
        ),
        type table [ ActivityDuraton = Int64.Type ]
    ),
    RemovedColumns = Table.RemoveColumns ( RepeatDuration, { "Activity Duration", "Days" } ),
    ExpandedCustom = Table.ExpandTableColumn (
        RemovedColumns,
        "Custom",
        { "ActivityDuraton" },
        { "ActivityDuraton" }
    ),
    AddedIndex = Table.AddIndexColumn ( ExpandedCustom, "Index", 1, 1, Int64.Type )
in
    AddedIndex