Forum Discussion

CCHarrison's avatar
CCHarrison
Regular Visitor
2 years ago
Solved

Splitting cell values and distributing them vertically

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:

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

 

Table 2. Desired format:

100X123[10] Text A
100X124[11] Text B
100X125[12] Text C
101X234[13] Text D
101X235[14] Text E
102X300[15] Text F
102X301[16] Text G
102X302[17] Text H
102X303[18] Text I
102X304[19] Text J
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago
    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"

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

  • CCHarrison's avatar
    CCHarrison
    Regular Visitor
    2 years ago

    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's avatar
      wdx223_Daniel
      Community Champion
      2 years ago

      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

       

  • CCHarrison's avatar
    CCHarrison
    Regular Visitor
    2 years ago

    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's avatar
      wdx223_Daniel
      Community Champion
      2 years ago
      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's avatar
        CCHarrison
        Regular Visitor
        2 years ago

        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!