Multiple Index in the same Column

[shrikantpanch]

Hi All,

There is a  scenario where i get data as shown in the tabel below. The Number of lines in the address for customers is not constant.  I am trying to add an Index column which will automatically  find out the number of lines so that i can draw the data from each line.

Any thoughts how it can be achieved?

Customer Name	Address	Index
XXX	Address line 1	1
	Address line 2	2
	Address line 3	3
	Address line 4	4
YYY	Address line 1	1
	Address line 2	2
ZZZ	Address line 1	1
	Address line 2	2
	Address line 3	3
	Address line 4	4
	Address line 5	5
AAAA	Address line 1	1
	Address line 2	2
	Address line 3	3
	Address line 4	4
BBB	Address line 1	1
	Address line 2	2
	Address line 3	3
	Address line 4	4
	Address line 5	5

Regards

[HotChilli]

I removed my answer.  I wasn't concentrating!

[HotChilli · Accepted solution]

If you use 'Fill Down' to get the Customer Name next to each relevant address, you can then do a Group By and count each item within the group

Advanced Editor Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiIUNJRckxJKUotLlbIycxLVTBMU4rViVZSQBc3wi5sjF3YBCwcGRmJLlGMz/SoqCgM51DPNRjCpmBhRyCgoa1OTk509lMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Address = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Address", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Customer Name", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Customer Name"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Customer Name"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Customer Name"}, {{"all", each _, type table [Customer Name=text, Address=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "SubCount", 1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"all"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Address", "SubCount"}, {"Custom.Address", "Custom.SubCount"})
in
#"Expanded Custom"

[shrikantpanch]

@HotChilli  Thank you for the suggesting this approach . Appreciate it .. Cheers 🙂 Shall give it a try.