Forum Discussion
Solved
Split Columns
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 delimite...
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.
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 |
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?