Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shrikantpanch
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

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
Super User
Super User

I removed my answer.  I wasn't concentrating!

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"

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors