Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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.