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...
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.
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?