Split Columns

[DPCCGF]

I need to split a column with a delimiter of "-".  However, this column does not always have an equal amount of, what I will call, splits.  Plus, I have a Project Name that sometimes has the delimiter in the name.  So if this column has splits that are 5, 6, 7 sections how does one split this and ensure each column as the appropriate data for that column?  Hope I am making sense.

[DPCCGF]

Well each split should have the same info in the split column.  So if Department is in column 6 of the first project it should fall into column 6 for all projects.  But it does not always follow what you would expect.  The split itself works fine, it is just that some projects split into 6 rows and some up to 10 rows.  And the bad part is row 4, let's say, does not always contain the same type of data, say Project Start Date.  Some projects have a "blank" when there is no data but others do not.  Hope that makes sense. 

[hnguy71]

Hi @DPCCGF ,

That's understood but without sample data and expected results I can't help you.

Sample input:

Column

A-B-C

1-b-2-3

D-E-F-4-g

Sample outout:

Column1	Column2	Column3	Column4
A	B	Column3
1	b	2	3
D-E	F	4	G

Also if you have business logic as to what should go where it would make support process faster.

[DPCCGF]

Here you go!

Project ID	Column to Split
283115	12068 - XXXXXXX - - TAX - Individual Tax - 104022 - 2022 1040 Individual Tax Return - YYYYYYY
283116	11721 - XXXXXXXX - 12/2022 - TAX - Business Tax - 106522 - 2022 1065 Partnership Return
283117	11721 - XXXXXXXXX - 12/2022 - TAX - Business Tax - 112022 - 2022 1120 Corporation Return
283118	11721 - XXXXXXXX - 12/2022 - AA: Audit - For Profit - BASICAUDFP22 - 2022 Basic Audit (FP)
283119	11696 - XXXXXXXX. - 12/2018 - OAFC - Monthly - 107627 - Monthly Accounting
283120	11696 - XXXXXXX. - 12/2022 - OAFC - Compliance - 990Prep2022 - 2022 990 Preparation
283121	11779 - XXXXXXXXXXXXX - 12/2023 - AA:Non Attest - 990 NFP Tax Return - 2023 990 Tax Form
283122	27503 - XXXXXXXXXXXXXXXXXXXX. - 12/2023 - AA:Non Attest - 990 NFP Tax Return - 2023 990 Tax Form
283123	67140 - XXXXXXXXXXXXXXXXXXXXXXXXXXXX - 6/2022 - AA:Non Attest - 125967 - 2022 990 Tax Return
283124	21456 - XXXXXXXXXXXXXXXXXXX. - 12/2022 - AA: Audit - NFP - BASICAUDNFP22 - 2022 Basic Audit (NFP)
283125	53024 - yyyyyyyyyyyyyyyyyyyC. - 12/2022 - AA: Audit - NFP - BASICAUDNFP22 - 2022 Basic Audit (NFP)
283126	12388 - LHHHHHHHHHHHHHH - - TAX - Fiduciary Tax - 104122 - 2022 1041 Estate and Trust Return
283127	12821 - HHHHHHHHHHHHHHHHHH. - 12/2023 - AA: Consulting - NON ATTEST CONSULTIN - OTHER NON ATTEST
283128	12560 - HHHHHHHHHHHHHHHHHHHHHHH - 12/2022 - AA: Audit - NFP - CS_58688 - 2022 Basic Audit (NFP) | 2022 NON PROFIT AUDIT
283129	17352 - KKKKKKKKKKKKKKKKKKKKKKKKK - 12/2022 - AA: Consulting - LeaseAcctg - Lease Accounting
283130	11941 - KKKKKKKKKKKKKKKKKKKKKKKKKKKKK. - 12/2022 - AA: Compilation - COMP22 - 2022 Compilation
283131	37403 - KKKKKKKKKKKKKKKKKKKKKKK- 12/2022 - AA: Audit - NFP - BASICAUDNFP22 - 2022 Basic Audit (NFP)
283132	37403 - AAAAAAAAAAAAAAAAAAAAAAA - 12/2021 - AA:Non Attest - 125311 - 2021 990 Tax Return
283133	37403 - AAAAAAAAAAAAAAAAAAAAAAAAA - 12/2017 - AA: Consulting - 98436 - OTHER NON ATTEST
283134	51107 - EEEEEEEEEEEEEEEEEEEEEEC. - 12/2022 - AA: Audit - NFP - CS_58791 - 2022 UG Audit | 2022 Non Profit Audit
283135	51107 - CCCCCCCCCCCCCCCCCC. - 6/2022 - AA: Audit - NFP - 58792 - 2022 UNIFORM FINANCIAL REPORT
283136	51107 - CCCCCCCCCCCCCCCCCCCCCCNC. - 12/2022 - AA: Consulting - LeaseAcctg - Lease Accounting
283137	12613 - GGGGGGGGGGGGGGG- - TAX - Consulting - COVID CONSULTING [TT - 2022 COVID CONSULTING
283138	74065 - GGGGGGGGGGGGGGGGGGGGGG - 6/2022 - AA:Non Attest - 122786 - 2021 990 Tax Return

[hnguy71]

Okay, now what's your expected output?

I'm not sure I understand the issue now that I'm seeing your sample input.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZZdb5swFIb/ipWrTWo7bIOB3Tk0tGgpoIRMm6JqQglbkVKI+JhWqT9+x0BiE0g6TT034A/54Ry/fs16PSEWxdiYXE0w0ZiFrtG3NuDtGkVcPL1sm/5Ot3W8Q1H8BzqwpmuEwAsRD9E6nbNIqrrIYMb3NiaPVx2KCRQ2CZYowcDkE2nXbJnTukyzpCyPRGaoRGagMC6qLCnKp3Tf4STEHIH8CwUTNS9oIScv9nkRV2meDSjWW6lw/hnxeptW8O7mBQqL/GfTmPKl5/DVrRtK3DQu0003/YMbfpQcu+EwmymcmwMIiz0LuOvA4yHPqqfdS1MvkxFT6eKbTV5nVZr9Oq5LtOG6N73v75Z18uf9Lo2zTQIN29bCItmrhYIuJPritk4SgNsCmba6Db0q0bZKPlSXV1VSVi0B+W7Y11EzV4yIXqjls6QQoBDT0OgpZZDTe9Ao0JiJQfLjNCVFpuigx8TEsJmplk/SJUkXeWHdYOOk/lapUhPpSI35Z0XmqyojwgMMqhEd5r4Mw3lvXmMEhFpCv/P7Xije44KnbNK4eJHWg3vWg9GsrOIqQXG2RVFRQ4FPC9m4AbGac3o/iIE8QO9ZWe/EWRHJBT7iUTRbRsgJ/OVqHnm+OBrR/WyhDEpa4wrEYNoo7ZDfpUo6yx+GxZrCjBcRvbYDAh8uAteLEBTeUz6isQyTGgLw5VwMPqOX+DyJywRsozo2xkyEtiZi6/gSScRQP8JX0l3rrZB18KAIRxmTLOEn1NSbk36G8q4apUQB8vE4ZoXHDzr4dwvC5w46pW9TJAebY5tlWzpll2RJhZsYGO4FmDUbjTdPeKNL08aHwq3uugkHOULq3RXX9Eu4ocCdQdyceOUJVjCPm7XyPTdYPCDX87nveHyOFrMwWCiJsossEf5Ipv8j/dZXGBb7dtcPaWC9hZ3gq3crfeQOraPoqPiTMckRjgLygH+eAaeLy5cNMS12ToOPfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Column to Split" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column to Split", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Some Id", "Some String", "Date It Seems", "Category", "Sub Category", "Sub Sub Category", "Note It Seems"})
in
    #"Split Column by Delimiter"

Split by Delimiter result:

What's your condition for where? Can you supply a sample output?

[hnguy71]

Hi @DPCCGF ,

Do you have sample data that you can share? Seems like you want some conditional split. It is possible but without any reference it would be hard to assist.

[FreemanZ]

hi @DPCCGF ,

try to split the column by each occurence of delimiter into rows. 

check more here:

https://learn.microsoft.com/en-us/power-query/split-columns-delimiter

[DPCCGF]

This worked but I now have project ids (which is the key) with 5 rows, 6 rows or 7 rows.  This is because the split column does not always have the same number of splits.  Hope I am making sense. I actually did a count of rows on the project Id and it ranges from 6-10

[FreemanZ · Accepted solution]

hi @hnguy71 ,

Could you describe how should the result look like?