Forum Discussion
Modifying text of a duplicated row after using List.Repeat
Hello,
I'm a beginner power query user, trying to create unique records of buildings based on a user's desired building number input. In exampe, user has asked for X number of buildings, and identified the types of floors in that building. I used list.Repeat to duplicate all building and floor counts based on the number of each building. But now I'm stuck with indistinguishable duplicate rows.
Current: Building floors are duplicated but indistinguishable from each other.
Desired: Each floor of each building is duplicated WITH a unique identifier Concatenated into the Building Type. Effectively looking to create unique record for that building's floor based on how many buildings are desired from column QTY BUILDING.
If Building C has a Ground floor and a Typical Floor, I need those identified by Building C1, C2, C3, C4, C5, C6
I've looked all over and can't find an answer here. I suspect it's obvious to any experienced user. Thank you
Sample Data:
| Building | Floor | Index | QTY Building |
| A | Underground | 1 | 1 |
| A | Ground | 2 | 1 |
| A | Typical | 3 | 1 |
| B | Underground | 4 | 1 |
| B | Ground | 5 | 1 |
| B | Typical | 6 | 1 |
| C | Ground | 7 | 6 |
| C | Ground | 8 | 6 |
| C | Ground | 9 | 6 |
| C | Ground | 10 | 6 |
| C | Ground | 11 | 6 |
| C | Ground | 12 | 6 |
| C | Typical | 13 | 6 |
| C | Typical | 14 | 6 |
| C | Typical | 15 | 6 |
| C | Typical | 16 | 6 |
| C | Typical | 17 | 6 |
| C | Typical | 18 | 6 |
| D | Ground | 19 | 2 |
| D | Ground | 20 | 2 |
| D | Typical | 21 | 2 |
| D | Typical | 22 | 2 |
| E | Ground | 23 | 4 |
| E | Ground | 24 | 4 |
| E | Ground | 25 | 4 |
| E | Ground | 26 | 4 |
| E | Typical | 27 | 4 |
| E | Typical | 28 | 4 |
| E | Typical | 29 | 4 |
| E | Typical | 30 | 4 |
| Site Parking | Ground | 31 | 1 |
| Landscaping | Ground | 32 | 1 |
Leslie743
Hi Leslie743 ,
From your sample data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZK9DoIwFEZfxTAz0BYojP7FxcFEnQhDA4QQTSWog28vqdpyLd/QpD3n5uttbosiWAZhcNZ1M7TD7anr8cTMKsOP2/0wJ/j06rtKXcedsHzlRcXE2aiEYBeVWr6elkujPJzN43weswhwBjgn3DXJBBIxEgkSKRISCffoDek2NxPyOI8Id0GcIcGt2JIkYebp8xjwBPCU8MnFEokMiRwIEVlx7B7N4qCGS6fbaRvC/fK90vW9Uv1/xffDl28=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Building = _t, Floor = _t, Index = _t, #"QTY Building" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Floor", type text}, {"Index", Int64.Type}, {"QTY Building", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Building", "Floor"}, {{"Count", each _, type table [Building=nullable text, Floor=nullable text, Index=nullable number, QTY Building=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Bldg Count", 1, 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index", "QTY Building", "Bldg Count"}, {"Index", "QTY Building", "Bldg Count"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each [Building] & Number.ToText([Bldg Count])) in #"Added Custom1"
4 Replies
- mussaenda
Community Champion
Hi Leslie743 ,
From your sample data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZK9DoIwFEZfxTAz0BYojP7FxcFEnQhDA4QQTSWog28vqdpyLd/QpD3n5uttbosiWAZhcNZ1M7TD7anr8cTMKsOP2/0wJ/j06rtKXcedsHzlRcXE2aiEYBeVWr6elkujPJzN43weswhwBjgn3DXJBBIxEgkSKRISCffoDek2NxPyOI8Id0GcIcGt2JIkYebp8xjwBPCU8MnFEokMiRwIEVlx7B7N4qCGS6fbaRvC/fK90vW9Uv1/xffDl28=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Building = _t, Floor = _t, Index = _t, #"QTY Building" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Floor", type text}, {"Index", Int64.Type}, {"QTY Building", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Building", "Floor"}, {{"Count", each _, type table [Building=nullable text, Floor=nullable text, Index=nullable number, QTY Building=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Bldg Count", 1, 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index", "QTY Building", "Bldg Count"}, {"Index", "QTY Building", "Bldg Count"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each [Building] & Number.ToText([Bldg Count])) in #"Added Custom1"
