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

[chris-thompson]

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)

[#2]

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

[#3 · Accepted 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]:

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

[#4]

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

[#5]

https://answers.microsoft.com/en-us/msoffice/forum/all/merging-multiple-columns-from-one-tab-into-a/...

https://club.excelhome.net/forum.php?mod=viewthread&tid=1661037&fromguid=hot&extra=&mobile&_dsign=b3...

In fact,I run online sql to settle this kind of questions.

[#6]

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.

[#7]

All roads lead to Roman.
If possible share your file,I can try it with online tool.

[#8]

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

[#9]

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

[#10]

@chris-thompson Glad this helped

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