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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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