Forum Discussion
Splitting cell values and distributing them vertically
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"
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.