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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CCHarrison
Regular Visitor

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
1 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"

View solution in original post

7 REPLIES 7
CCHarrison
Regular Visitor

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...

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"

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!

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
Regular Visitor

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.

it's ok on my PC

wdx223_Daniel_0-1700714029806.png

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors