March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Would anyone know how I can duplicate the rows of a query based on a value in a column?
Here's an example:
ID | Installments |
1357 | 5 |
1606 | 3 |
1126 | 2 |
To:
ID | Installments |
1357 | 1 |
1357 | 2 |
1357 | 3 |
1357 | 4 |
1357 | 5 |
1606 | 1 |
1606 | 2 |
1606 | 3 |
1126 | 1 |
1126 | 2 |
Solved! Go to Solution.
Hi,
Try this M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2NVfSUTJVitUBcswMzIAcYwjH0AjEMVKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Installments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Installments", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From(1)..Number.From([Installments])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Installments"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Instalments"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Instalments", Int64.Type}})
in
#"Changed Type1"
Here is the result
Thanks Pietro & Ashish for this question and solution! Very helpful for adding/duplicating rows based on a column variable so that each unique instance can be assigned a primary key.
You are welcome.
Hi,
Try this M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2NVfSUTJVitUBcswMzIAcYwjH0AjEMVKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Installments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Installments", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From(1)..Number.From([Installments])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Installments"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Instalments"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Instalments", Int64.Type}})
in
#"Changed Type1"
Here is the result
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
165 | |
116 | |
63 | |
57 | |
50 |