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