SOLVED

How to Split Multiple Columns Into Multiple Rows with Separate Groups of Columns

Copper Contributor

Hi all.  I have a Microsoft Form that gathers all of the interventions that schools use with their students including specific information about each intervention (Type - Academic/Behavior; Subject - Math/Reading; Tier Level - Tier 1/2/3; and Grade Levels K;1st;2nd;3rd;4th;5th;6th;7th;8th;9th;10th;11th;12th).  Each intervention (and it's associated characteristics) are in separate columns.  How do I transform the data so that the data table looks like the third table below?  (The image is also attached).  

 

I want to use PowerQuery and keep everything within Excel (not PowerBI or databases)

 

christhompson_0-1683137461496.png

 

9 Replies
Type - Academic/Behavior; Subject - Math/Reading; Tier Level - Tier 1/2/3; and Grade Levels K;1st;2nd;3rd;4th;5th;6th;7t

as far as I know need data cleaning with regular expression.

why
not PowerBI or databases)?
best response confirmed by HansVogelaar (MVP)
Solution

Hi @chris-thompson 

 

Your 1st challenge is exactly the same as Automatic Data entry manipulation where @CWsChim was smart enough not to mark any possible solution so you almost had no chance to find it when you Searched :-((

 

For your 2nd expected view, the approach is the same. Once you have your first view, Split column [Grade Levels] by Delimiter to Rows then do the same for [Tier Levels]:

Sample.png

 

Next time(s) please share a sample workbook, recreating data takes unecessary time - Thanks

Because PowerBI is not accessible to anyone outside of me (the organizations I serve will not have premium licenses), also, I know very very little about how to create a database, but I know a fair amount about Excel. In terms of your feedback, I have no idea what you mean. Could you clarify what you mean by "I know need data cleaning with regular expression"?
I appreciate the suggestions but none of them really address how I get from the data looking the way it does in the first table of my document, to the bottom table. That is what I need to be done.
All roads lead to Roman.
If possible share your file,I can try it with online tool.

@chris-thompson 

how I get from the data looking the way it does in the first table of my document, to the bottom table. That is what I need to be done

Are you saying the proposal I shared doesn't do it? Did I miss something?

I just tested it out and it works! Thank you so much!

@chris-thompson Glad this helped

& Thanks @HansVogelaar for marking the solution on your behalf - This helps those who Search so don't forget next time(s) please

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

Hi @chris-thompson 

 

Your 1st challenge is exactly the same as Automatic Data entry manipulation where @CWsChim was smart enough not to mark any possible solution so you almost had no chance to find it when you Searched :-((

 

For your 2nd expected view, the approach is the same. Once you have your first view, Split column [Grade Levels] by Delimiter to Rows then do the same for [Tier Levels]:

Sample.png

 

Next time(s) please share a sample workbook, recreating data takes unecessary time - Thanks

View solution in original post