Modifying text of a duplicated row after using List.Repeat

[Leslie743]

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

[mussaenda · Accepted solution]

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"

[Leslie743]

Thank you @mussaenda - the Source = Table.FromRows (Json...) would that instead by referenced to my last step? Apologies, not familiar with what that large address would be or if it exists because I put the sample data inside the message instead of as a file.

[Leslie743]

Thank you, worked perfectly once I referenced my own data

[mussaenda]

Hi @Leslie743 ,

For the source, you need to change it according to your own source.

You can start doing the steps from grouped rows to achieve your desired result