Forum Discussion

Frequent Visitor

4 months ago

Solved

Add duplicate row based on column

I have a database with steps applied to it on Power Query, I would like Add the number of rows
to my table based on the "phaseCount" . Example of what I want with a before and after:

before

fid	phaseCount	phase	extractDate	deviceName
201800722	3	ABC	03-Mar-26	Device1
201458541	1	A	03-Mar-26	Device2
201801230	1	B	03-Mar-26	Device3
110220928	2	BC	03-Mar-26	Device4

After

fid	phaseCount	phase	extractDate	deviceName
201800722	3	ABC	03-Mar-26	Device1
201800722	3	ABC	03-Mar-26	Device1
201800722	3	ABC	03-Mar-26	Device1
201458541	1	A	03-Mar-26	Device2
201801230	1	B	03-Mar-26	Device3
110220928	2	BC	03-Mar-26	Device4
110220928	2	BC	03-Mar-26	Device4

Here is my Power Query

let
Source = Sql.Databases("SQLUTLCLP-123\PROD02,00000"),
DMS = Source{[Name="DMS"]}[Data],
eccl_gisDeviceChanges = DMS{[Schema="eccl",Item="gisDeviceChanges"]}[Data],
#"Extracted Date" = Table.TransformColumns(eccl_gisDeviceChanges,{{"extractDate", DateTime.Date, type date}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Date", each ([extractDate] <> null and [extractDate] <> #date(2025, 7, 7))),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"fid", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Inserted Cleaned Text" = Table.AddColumn(#"Added Index", "Clean", each Text.Clean(Text.From([Index], "en-US")), type text),
#"Inserted Trimmed Text" = Table.AddColumn(#"Inserted Cleaned Text", "Trim", each Text.Trim([Clean]), type text),
#"Added Index1" = Table.AddIndexColumn(#"Inserted Trimmed Text", "Index.1", 0, 1, Int64.Type),
#"Inserted Trimmed Text1" = Table.AddColumn(#"Added Index1", "Trim.1", each Text.Trim(Text.From([Index.1], "en-US")), type text),
AddedCustom =
Table.AddColumn (
ChangedType,
"Custom",
each Table.Repeat ( Table.FromRecords ( { _ } ), _[phaseCount] ),
ColumnAndTypes
),
RemovedColumns = Table.RemoveColumns ( AddedCustom, { "deviceName", "fid", "phaseCount" } ),
ExpandedCustom =
Table.ExpandTableColumn (
RemovedColumns,
"Custom",
ColumnNames,
ColumnNames
),
#"Kept Duplicates" = let columnNames = {"fid"}, addCount = Table.Group(#"Added Index", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Added Index", columnNames, removeCount, columnNames, JoinKind.Inner),
#"Filtered Rows1" = Table.SelectRows(#"Kept Duplicates", each true)
in
#"Kept Duplicates"

Show and Tell

Natarajan_M

4 months ago

Hi jwb3d , from your query, I beleive you need to duplicate same rows if the Phase count is more than one value .

IP :

Use Table .Repeat fundtion to handle this scenario

let
    Source = #"IP Table", // -> Source table

    ColNames = Table.ColumnNames(Source),

    // Nest each row as a repeated table
    AddedRepeated = Table.AddColumn(
        Source,
        "RepeatedRows",
        each Table.Repeat( Table.FromRecords({ _ }), [phaseCount] )
    ),

    // the id is causing error so drop originals BEFORE expanding to avoid duplicate field error
    OnlyNested = Table.SelectColumns(AddedRepeated, {"RepeatedRows"}),

    Expanded = Table.ExpandTableColumn(
        OnlyNested, "RepeatedRows", ColNames
    ),
    #"Added Index" = Table.AddIndexColumn(Expanded, "Index", 1, 1, Int64.Type)

in
    #"Added Index"

OP:

Sample :
Row Expand.pbix

Thanks
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

ZhangKun

4 months ago

if you want to add duplicate rows, you can add a list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnIGlgrOubWKRrZAZku6SWZSanGirF6oBVm5hamJoYAsVB2BGrWiOYWgsDQyNjA6haJ6xqjcFqDQ0NjIwMLI0sgOIgl+BwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"fid", Int64.Type}, {"phaseCount", Int64.Type}, {"phase", type text}, {"extractDate", type date}, {"deviceName", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,null,(old,new,z)=>List.Repeat({old}, Text.Length(old)),{"phase"}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Replaced Value", "phase")
in
    #"Expanded {0}"

Ahmedx

4 months ago

pls try

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnIGlgrOubWKRrZAZku6SWZSanGirF6oBVm5hamJoYAsVB2BGrWiOYWgsDQyNjA6haJ6xqjcFqDQ0NjIwMLI0sgOIgl+BwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"phaseCount", type number}}),
 lst = Table.ColumnNames(#"Changed Type"),
 from = List.TransformMany(Table.ToRows(#"Changed Type"), (x)=> {1..x{1}},(x,y)=>x),
 tb = Table.FromRows(from,lst)

in
    tb

1002.pbix23 KB

6 Replies

ZhangKun

Super User

4 months ago

if you want to add duplicate rows, you can add a list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnIGlgrOubWKRrZAZku6SWZSanGirF6oBVm5hamJoYAsVB2BGrWiOYWgsDQyNjA6haJ6xqjcFqDQ0NjIwMLI0sgOIgl+BwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"fid", Int64.Type}, {"phaseCount", Int64.Type}, {"phase", type text}, {"extractDate", type date}, {"deviceName", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,null,(old,new,z)=>List.Repeat({old}, Text.Length(old)),{"phase"}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Replaced Value", "phase")
in
    #"Expanded {0}"

Natarajan_M

Super User

4 months ago

Hi jwb3d , from your query, I beleive you need to duplicate same rows if the Phase count is more than one value .

IP :

Use Table .Repeat fundtion to handle this scenario

let
    Source = #"IP Table", // -> Source table

    ColNames = Table.ColumnNames(Source),

    // Nest each row as a repeated table
    AddedRepeated = Table.AddColumn(
        Source,
        "RepeatedRows",
        each Table.Repeat( Table.FromRecords({ _ }), [phaseCount] )
    ),

    // the id is causing error so drop originals BEFORE expanding to avoid duplicate field error
    OnlyNested = Table.SelectColumns(AddedRepeated, {"RepeatedRows"}),

    Expanded = Table.ExpandTableColumn(
        OnlyNested, "RepeatedRows", ColNames
    ),
    #"Added Index" = Table.AddIndexColumn(Expanded, "Index", 1, 1, Int64.Type)

in
    #"Added Index"

OP:

jwb3d

Frequent Visitor

4 months ago

I would like to include a column that is derived from the deviceName and phase. This would involve appending A-B-C to each of the duplicated rows.

Example

fid	phaseCount	phase	extractDate	deviceName	Index	New Column
201800722	3	ABC	3/3/2026	Device1	1	Device1A
201800722	3	ABC	3/3/2026	Device1	2	Device1B
201800722	3	ABC	3/3/2026	Device1	3	Device1C
821047293	2	AC	3/3/2026	Device10	19	Device1A
821047293	2	AC	3/3/2026	Device10	20	Device1B
720183650	1	D	3/3/2026	Device9	18	Device9D

Ahmedx

Super User

4 months ago

pls try

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnIGlgrOubWKRrZAZku6SWZSanGirF6oBVm5hamJoYAsVB2BGrWiOYWgsDQyNjA6haJ6xqjcFqDQ0NjIwMLI0sgOIgl+BwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"phaseCount", type number}}),
 lst = Table.ColumnNames(#"Changed Type"),
 from = List.TransformMany(Table.ToRows(#"Changed Type"), (x)=> {1..x{1}},(x,y)=>x),
 tb = Table.FromRows(from,lst)

in
    tb

1002.pbix23 KB

Ahmedx

Super User

4 months ago

This is optimized code, try it.

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnIGlgrOubWKRrZAZku6SWZSanGirF6oBVm5hamJoYAsVB2BGrWiOYWgsDQyNjA6haJ6xqjcFqDQ0NjIwMLI0sgOIgl+BwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"phaseCount", type number}}),
 from = Table.FromRecords( List.TransformMany(
     Table.ToRecords(#"Changed Type"),(x)=> {1.. Record.FieldValues(x){1}},(x,y)=>x))
in
    from

dufoq3

Community Champion

4 months ago

Hi jwb3d, another solution:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnIGlgrOubWKRrZAZku6SWZSanGirF6oBVm5hamJoYAsVB2BGrWiOYWgsDQyNjA6haJ6xqjcFqDQ0NjIwMLI0sgOIgl+BwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
    PhaseCountToList = Table.TransformColumns(Source,{{"phaseCount", each let n = Number.From(_) in List.Repeat({n}, n) , type {number}}}),
    ExpandedPhaseCount = Table.ExpandListColumn(PhaseCountToList, "phaseCount")
in
    ExpandedPhaseCount

Forum Discussion

Add duplicate row based on column

6 Replies

Related content

Remove duplicates based on multiple columns

Event based trigger causing duplicate runs

Duplicate ranking

Merge duplicates data in columns

Column count duplicates