Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@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

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thank you AntrikshSharma!

 

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

 

Regards,

Vesta 

AntrikshSharma
Community Champion
Community Champion

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors