Forum Discussion
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 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.
8 Replies
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
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
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.
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
