Filtering Table into Multiple Columns

[a68tbird]

Hello All -
Here is my scenario. I have reports that show radio plays of songs based on an artist. Each report is specific to one artist, but has multiple levels of data that need to be separated out. The problem is that the data is all contained within two columns, with column 1 containing what should be multiple headers. Here is an example of the data:

Column1	Column2
Region	Plays
North America	72
Oceania	2
Country	Plays
US	69
Canada	3
Australia	2
Song	Plays
Title 1	55
Title 2	19
Channel	Actual
WGWG Radio	13
KZE 98.1	6
KNWT 89.1 FM Wyoming Sounds	4
KFMG 98.9 FM	4
WRKC 88.5 FM	3
WMOT Roots Radio 89.5 FM	2
WPKN 89.5 FM	2
WFHB 91.3 FM	2
KOZT The Coast - Mendocino County's FM	2
WRIU 90.3 FM	1
WNCW NPR 88.7 FM	1
Others	34

So in this sample, Column 1 has four distinct areas that I'd like to separate: Region, Country, Song and Channel. I guess this would have to result in four new tables as there really isn't any way to flatten the info into one table. Also to note, each of those sections could have more or fewer entries - each downloaded report would have different values, but those headers remain constant.

Love to hear some ideas on how to accomplish this.

Thanks

 

[shaowu459]

Table.Group could achive below result, each section in one table:

You can transform each table to the final layout you want to get and then combine them.

 

[shaowu459]

for example, you can get below by expand table column:

= Table.ExpandTableColumn(Table.Group(Source,"Column1",{"n",each Table.Skip(_)},0,(x,y)=>Byte.From(List.Contains({"Region","Country","Song","Channel"},y))),"n",{"Column1","Column2"},{"Title1","Title2"})

 

[a68tbird]

Amazing!! That works perfectly. Thank you so much!

 

[shaowu459]

Amazing!! That works perfectly. Thank you so much!

You are welcome and thanks for the feedback