Power Query - split to columns by capital letter

[pepe74287]

Hello,

Another question related to Column split. This time I would need to split data into 2 columns, where delimiter is last capital letter.

For instance:

Robert DuvallCrime
TabuDrama

Should be splitted as:

[table="width: 500"]
[tr]
[td]Column1[/td]
[td]Column2[/td]
[/tr]
[tr]
[td]Robert Duvall[/td]
[td]Crime[/td]
[/tr]
[tr]
[td]Tabu[/td]
[td]Drama[/td]
[/tr]
[/table]

Thanks
Pepe

 

[billszysz]

Try this code below

let
    source = Table.FromColumns({ {"Robert DuvallCrime", "TabuDrama"} }, {"text"}),
    AddCol = Table.AddColumn(source, "Custom", each Record.FromList(Splitter.SplitTextByPositions({0,Text.PositionOfAny([text], {"A".."Z"}, 1)}) ([text]), {"Column1", "Column2"})),
    ExpandRecordColumn = Table.ExpandRecordColumn(AddCol, "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    RemoveCol = Table.RemoveColumns(ExpandRecordColumn,{"text"})
in
    RemoveCol

Regards

 

[pepe74287]

Bill,

ExcelIsFun is right, you truly are Power Query Wizard

Thanks again, Pepe