Split delimited data into n columns keeping any more than n in last column

[k1s1]

Hello I've got data in a column delimited (with "| ") that I want to split into 6 columns: the last one containing the 6th plus any more data items, retaining a delimiter.

So in the example below, the second last row contains 8 items to be split over 6 columns with the last 3 all in one column 

Test

Routine blood tests| MRI - Head

Routine blood tests| Tumour markers| CT - Colon

Routine blood tests| X-ray other

CT - Chest Abdomen Pelvis| Routine blood tests| PET scan

Routine blood tests| CT - Chest Abdomen Pelvis| CT - Colon| Endoscopy| ECG| MRI - Pancreas| X-ray other| Other - See Comments

X-ray other

If I use something like:

#"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "Test", Splitter.SplitTextByDelimiter("| ", QuoteStyle.Csv), {"Test.1", "Test.2", "Test.3", "Test.4", "Test.5", "Other Tests"}),

...it produces this:

Test.1	Test.2	Test.3	Test.4	Test.5	Other Tests
Routine blood tests	MRI - Head	null	null	null	null
Routine blood tests	Tumour markers	CT - Colon	null	null	null
Routine blood tests	X-ray other	null	null	null	null
CT - Chest Abdomen Pelvis	Routine blood tests	PET scan	null	null	null
Routine blood tests	CT - Chest Abdomen Pelvis	CT - Colon	Endoscopy	ECG	MRI - Pancreas
X-ray other	null	null	null	null	null

Which seems to truncate/ignore/delete the last 2 items of the second last row,

Any ideas for how I can make it produce this instead?:

Test.1	Test.2	Test.3	Test.4	Test.5	Other Tests
Routine blood tests	CT - Chest Abdomen Pelvis	CT - Colon	Endoscopy	ECG	MRI - Pancreas| X-ray other| Other - See Comments

[k1s1]

Hello - many thanks for your reply and solution.  I'm trying to work out how you did it.

Would you mind exlaining this please:

    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "beforesix", each Text.BeforeDelimiter([Colonna1],"|",4)),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "AfterSix.1", each Text.AfterDelimiter([Colonna1],"|",4)),

[Anonymous · Accepted solution]

I divided the text into two parts: the one before the fifth "|" (in M the positions start from the 0-th) nd the part following the fifth "|",  obtaining two columns Following your requests, I applied the splitting only to the first part contained in the first column of these two.

dai un'occhiata qui 

[k1s1]

Ah, thank you

I tried to replicate it with some English to help me!

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDIZfJezsHkLKUA9imTsIY4euDWzYNtJ2wmAPb5yIDNxOSfj/L/lT11lJQ+o9QmuJDCSMKU5wLk+QwxGVyZrdiqcaHA0BnAp3DDyLihFBlvw6c8uDGoFSh2E2fZiORdi3hhx6kGifPVv/8rKoIGq1cWFj4y/gBIU3FDU9Rm7F4fuwVF4HVMugE1zeheUrIvOOd6Y4J1i807wA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Origine,{{"Colonna1", "Tests"}}),
    #"Copy first five to new column" = Table.AddColumn(#"Renamed Columns", "Test", each Text.BeforeDelimiter([Tests],"|",4)),
    #"Copy remainder to new column" = Table.AddColumn(#"Copy first five to new column", "Other Tests", each Text.AfterDelimiter([Tests],"|",4)),
    #"Split first five by delimiter" = Table.SplitColumn(#"Copy remainder to new column", "Test", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Test.1", "Test.2", "Test.3", "Test.4", "Test.5"})
in
    #"Split first five by delimiter"

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDIZfJezsHkLKUA9imTsIY4euDWzYNtJ2wmAPb5yIDNxOSfj/L/lT11lJQ+o9QmuJDCSMKU5wLk+QwxGVyZrdiqcaHA0BnAp3DDyLihFBlvw6c8uDGoFSh2E2fZiORdi3hhx6kGifPVv/8rKoIGq1cWFj4y/gBIU3FDU9Rm7F4fuwVF4HVMugE1zeheUrIvOOd6Y4J1i807wA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "beforesix", each Text.BeforeDelimiter([Colonna1],"|",4)),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "AfterSix.1", each Text.AfterDelimiter([Colonna1],"|",4)),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Aggiunta colonna personalizzata1", "beforesix", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"beforesix.1", "beforesix.2", "beforesix.3", "beforesix.4", "beforesix.5"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"Colonna1", type text}, {"beforesix.1", type text}, {"beforesix.2", type text}, {"beforesix.3", type text}, {"beforesix.4", type text}, {"beforesix.5", type text}})
in
    #"Modificato tipo"