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  Activity duration days 1 2 2 5 3 1   I want ...
  • AntrikshSharma's avatar
    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