Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Add index based on a column

Hi 🙂

 

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

Example: 

I have an activity with the duration in days 

Activityduration days
12
25
3

1

 

I want to expand the days to repeat the activities

ActivityDay
11
12
23
24
25
26
27
38
Need Help
Tips and Tricks
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you AntrikshSharma!

     

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

     

    Regards,

    Vesta 

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    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