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"
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])