cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k1s1
Helper I
Helper I

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

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.1Test.2Test.3Test.4Test.5Other Tests
Routine blood testsMRI - Headnullnullnullnull
Routine blood testsTumour markersCT - Colonnullnullnull
Routine blood testsX-ray othernullnullnullnull
CT - Chest Abdomen PelvisRoutine blood testsPET scannullnullnull
Routine blood testsCT - Chest Abdomen PelvisCT - ColonEndoscopyECGMRI - Pancreas
X-ray othernullnullnullnullnull

 

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.1Test.2Test.3Test.4Test.5Other Tests
Routine blood testsCT - Chest Abdomen PelvisCT - ColonEndoscopyECGMRI - Pancreas| X-ray other| Other - See Comments
1 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 

View solution in original post

4 REPLIES 4
k1s1
Helper I
Helper I

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)),

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 

View solution in original post

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"

 

 

Rocco_sprmnt21
Super User II
Super User II

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"

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors