cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shrikantpanch Frequent Visitor
Frequent Visitor

Multiple Index in the same Column

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 NameAddressIndex
XXXAddress line 11
 Address line 22
 Address line 33
 Address line 44
YYYAddress line 11
 Address line 22
ZZZAddress line 11
 Address line 22
 Address line 33
 Address line 44
 Address line 55
AAAAAddress line 11
 Address line 22
 Address line 33
 Address line 44
BBBAddress line 11
 Address line 22
 Address line 33
 Address line 44
 Address line 55

 

 

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli New Contributor
New Contributor

Re: Multiple Index in the same Column

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"

 

View solution in original post

3 REPLIES 3
HotChilli New Contributor
New Contributor

Re: Multiple Index in the same Column

I removed my answer.  I wasn't concentrating!

HotChilli New Contributor
New Contributor

Re: Multiple Index in the same Column

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"

 

View solution in original post

shrikantpanch Frequent Visitor
Frequent Visitor

Re: Multiple Index in the same Column

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

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,791)