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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    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.