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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
matherhorn64
Frequent Visitor

Count number of times table was duplicated in Power Query

Hi All,

 

I have a certain table ("Table") in Power Query and I'm using Table.Repeat function duplicate it for X number of times (X is coming from another table). What I want to do is to add to the "Table" new column which would show what duplicatation sequence it is.

 

For example, in "Table" I have 100 rows and I want to duplicated it for 3 times, so this new column should show me "1" for rows 1-100, "2" for rows 101-200, "3" for rows 201-300.

 

Any ideas how to do it?

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

You can add a 0-based Index column, Integer-Divide the values by the number of rows of the original table and add 1.

 

let
    Source = Table1,
    Repeated = Table.Repeat(Source,Table.FirstValue(Table2)),
    #"Added Index" = Table.AddIndexColumn(Repeated, "Duplication", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Duplication", each 1 + Number.IntegerDivide(_, Table.RowCount(Source)), Int64.Type}})
in
    #"Integer-Divided Column"
Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug
Community Champion
Community Champion

You can add a 0-based Index column, Integer-Divide the values by the number of rows of the original table and add 1.

 

let
    Source = Table1,
    Repeated = Table.Repeat(Source,Table.FirstValue(Table2)),
    #"Added Index" = Table.AddIndexColumn(Repeated, "Duplication", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Duplication", each 1 + Number.IntegerDivide(_, Table.RowCount(Source)), Int64.Type}})
in
    #"Integer-Divided Column"
Specializing in Power Query Formula Language (M)

Wonderful, thanks again!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.