How to split a column by words

Hi Manumitec ,
This should give you enough to go on.

1.Count the spaces

2.Build the logic based on number of spaces

3.Add conditions to your final 3 columns such as seen in picture below.

4. Build other columns to support your logic.

One thing that wasn't clear, was what a value was...water is the Format 2L or 2L Bottle and came in a package of 6. You would be well served to delineate this on paper first so that you know what columns you have to create, and how you are going to get them.  Also naming convention in the steps, such as I did for the 3 main columns.  At the end, delete all columns but the 3.  Use [space] as else value  so you don't get a null.

A lot of work!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Paste this into the Advanced Editor of a Blank Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTM4uVjAy8HZXCEjMyUktUVCK1YlWAhNBqSkK4fn5KQqmQFmn/AoFE4RceGJJapGCkQ9QvKQkJ1XBDCEVnJiXguC5ZBaXZAINBhoF1mII06IUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
    #"Removed Blank Rows1" = Table.SelectRows(#"Cleaned Text", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Count Space" = Table.AddColumn(#"Removed Blank Rows1", "Count space", each List.Count(Text.Split([Column1]," "))-1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Count Space",{{"Count space", Int64.Type}}),
    Custom = Table.AddColumn(#"Changed Type1", "Custom", each if [Count space] = 2 then "split by space" else if [Count space] = 0 then "split by space" else if [Count space] = 3 then "if first value is digit split there" else if [Count space] = 4 then "find first digit and split there" else null),
    #"Inserted Text Before Delimiter" = Table.AddColumn(Custom, "Text Before Delimiter", each Text.BeforeDelimiter([Column1], " "), type text),
    #"Inserted Text Before Delimiter1" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text Before Delimiter.1", each Text.BeforeDelimiter([Column1], " ", 1), type text),
    #"Inserted Text Before Delimiter2" = Table.AddColumn(#"Inserted Text Before Delimiter1", "Text Before Delimiter.2", each Text.BeforeDelimiter([Column1], " ", 2), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter2", "Text After Delimiter", each Text.AfterDelimiter([Column1], " ", 2), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text Before Delimiter", "0 or2"}}),
    Name = Table.AddColumn(#"Renamed Columns", "Name", each if [Count space] = 2 then [0 or2] else if [Count space] = 4 then [Text Before Delimiter.1] else " "),
    #"Changed Type2" = Table.TransformColumnTypes(Name,{{"Name", type text}}),
    #"Inserted Text After Delimiter1" = Table.AddColumn(#"Changed Type2", "Text After Delimiter.1", each Text.AfterDelimiter([Column1], " ", {0, RelativePosition.FromEnd}), type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Text After Delimiter1",{{"Text After Delimiter.1", "After delimiter"}}),
    Package = Table.AddColumn(#"Renamed Columns1", "Packaging", each if [Count space] = 2 then [After delimiter] else if [Count space] = 4 then [Text After Delimiter] else "  "),
    #"Inserted Text Between Delimiters" = Table.AddColumn(Package, "Text Between Delimiters", each Text.BetweenDelimiters([Column1], " ", " "), type text),
    Format = Table.AddColumn(#"Inserted Text Between Delimiters", "Format", each if [Count space] = 2 then [Text Between Delimiters] else " "),
    #"Changed Type3" = Table.TransformColumnTypes(Format,{{"Format", type text}, {"Packaging", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"Name", "Format", "Packaging", "Column1", "Count space", "Custom", "0 or2", "Text Before Delimiter.1", "Text Before Delimiter.2", "Text After Delimiter", "After delimiter", "Text Between Delimiters"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Column1", "Original"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Custom", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type4",{{"Custom", "Logic"}}),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Renamed Columns3", "Text Between Delimiters.1", each Text.BetweenDelimiters([Original], " ", " ", 1, 0), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Package", each if [Count space] = 2 then [Text Between Delimiters] else if [Count space] = 4 then [Text Between Delimiters.1] else " "),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Package", type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type5",{"Name", "Format", "Package", "Packaging", "Original", "Count space", "Logic", "0 or2", "Text Before Delimiter.1", "Text Before Delimiter.2", "Text After Delimiter", "After delimiter", "Text Between Delimiters", "Text Between Delimiters.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Format"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"Package", "Format"}})
in
    #"Renamed Columns4"