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 ...
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

     

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