Forum Discussion

Deepak Sharma's avatar
Deepak Sharma
Copper Contributor
Apr 17, 2018

Split data from one column without using text to column

Hi,   I have some data(Invoice Nos) in a col A in a text format separated with"/", i want to split the same with using a formula (NOT TEXT TO COLUMN) coz if new data is added I'll need to go throug...
Formulas & Functions
tips and tricks
JKPieterse's avatar
JKPieterse
Silver Contributor
Apr 17, 2018
Use Data, From Table then click the Transform tab on the powerquery editor and click SPlit column. Fill in the details and you're good to go. If your source data changes, simply click refresh all on the data tab.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 17, 2018

    As a comment - if it is possible variable number of separators on rows number of columns is to be calculated additionally, something like this

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    NumberOfDelimeters = Table.AddColumn(ToText, "Parts", each List.Count(Text.Split([Column1], "/"))-1),
    TotalColumns = List.Max(NumberOfDelimeters[Parts])+1,
    ColumnsList = List.Transform({1..TotalColumns}, each "P"&Text.From(_)),
    SplitColumns = Table.SplitColumn(ToText, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), ColumnsList)

in
    SplitColumns

    and attached

Resources