Splitting cell values and distributing them vertically

[CCHarrison]

Hi,

My client has a data table formatted as in the example below (dummy data). The concatenated values in column B need to be separated at the X, and then individually associated with the values in Col C in the way shown in Table 2, i.e. the first Col B value goes with the 1st row in its set, the second value with the second row, thre third with the third and so on. The number of concatenated values in Table 1, Col B is unpredicatble, but their order (left to right) will always match the vertical order of the associated values in Col C. The values in Col C, as shown, all start with a unique sequential number in square brackets.

I know how to achieve the separation of the values in Col B (using split columns to rows), but what I can't work out is how to then selectively remove the duplicated ColB-ColC pairs to only leave the ones I want. Remove duplicates doesn't work, as it always keep the1st token, when I need it to keep the no. token that matches the row number in the repetition set, if that makes sense.

Or maybe there's just a better way?

My client does not allow VBA solutions, not that that should be necessary here I wouldn't think, but just so you know.

Many thanks.

Table 1. Original data format:

A	B	C
100	X123X124X125	[10] Text A
100	X123X124X125	[11] Text B
100	X123X124X125	[12] Text C
101	X234X235	[13] Text D
101	X234X235	[14] Text E
102	X300X301X302X303X304	[15] Text F
102	X300X301X302X303X304	[16] Text G
102	X300X301X302X303X304	[17] Text H
102	X300X301X302X303X304	[18] Text I
102	X300X301X302X303X304	[19] Text J

Table 2. Desired format:

100	X123	[10] Text A
100	X124	[11] Text B
100	X125	[12] Text C
101	X234	[13] Text D
101	X235	[14] Text E
102	X300	[15] Text F
102	X301	[16] Text G
102	X302	[17] Text H
102	X303	[18] Text I
102	X304	[19] Text J

[CCHarrison]

Hi again. Thanks for sticking with me. The data in my example was dummy data - the actual dataset has 13 columns, and the columns corresponding to A, B and C in my dummy set are actually Column2, Column4 and Column5 (i.e. non contiguous).

Also, my token dividers are actually "PO"s not "X"s, and I need to keep them, which is why I have added a ":" before each PO and used ":" as the divider (the "Replaced Value" line).

So, my code is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"PO",":PO",Replacer.ReplaceText,{"Column4"}),
    #"Custom1" = Table.Combine(Table.Group(Source,{"Column2","Column4"},{"n",each Table.FromColumns({[Column2],Splitter.SplitTextByCharacterTransition({"0".."9"},{":"})([Column4]{0}),[Column5]},Table.ColumnNames(_))})[n])
in
    #"Custom1"

When I run this, I get

Expression.Error: The count of 'columns' (3) doesn't match that of 'columnNames' (13).
Details:
    [List]

As I don't understand the syntax of your solution (sorry, not fluent in MCode), I'm not sure where this count of 'columns' = "3" is coming from, nor why the fact that it is not matching the count of columnNames is a problem...

[wdx223_Daniel · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"PO",":PO",Replacer.ReplaceText,{"Column4"}),
    #"Custom1" = Table.Combine(Table.Group(Source,{"Column2","Column4"},{"n",each Table.FromColumns(List.ReplaceRange(Table.ToColumns(_),3,1,{Splitter.SplitTextByCharacterTransition({"0".."9"},{":"})([Column4]{0})}),Table.ColumnNames(_))})[n])
in
    #"Custom1"

[CCHarrison]

Thanks Daniel. That works. I have to admit, I'm still not clear how your Mcode is actually achieving this. If you felt like stepping through it, I'd love to understand. But if you don't feel like doing that, that's OK. Cheers!

[wdx223_Daniel]

firstly, Table.Group to split table by column2 and column4

secondly, Splitter.SplitTextByCharacterTransition({"0".."9"},{":"}) to split the text of column4, at where a number followed by a colon, then we get a list.

next, use this list to replace the old column4, that the job of List.ReplaceRange

finally, combine all new tables together.

[CCHarrison]

Hi Daniel,

Thanks for your reply. I'm afraid I don't understand it. What is "Your Table" referring to? If I change that to the name of the Query, I get a cyclic reference error, and if I change it to the name of the source table in Excel, I get an expression error: name not recognised. Can you tell me what the variables I have to modify are, and what I need to point them to? Thanks.

[wdx223_Daniel]

it's ok on my PC

where did you put the code?

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Custom1 = Table.Combine(Table.Group(Source,{"A","B"},{"n",each Table.FromColumns({[A],Splitter.SplitTextByCharacterTransition({"0".."9"},{"X"})([B]{0}),[C]},Table.ColumnNames(_))})[n])
in
    Custom1

[wdx223_Daniel]

NewStep= Table.Combine(Table.Group(YourTable,{"A","B"},{"n",each Table.FromColumns({[A],Splitter.SplitTextByCharacterTransition({"0".."9"},{"X"})([B]{0}),[C]},Table.ColumnNames(_))})[n])