Change the format of some data from a table to a list

[rpg1966]

Hello everyone,
I have a requirement to change the format of some data from a table to a list.

The data I have at the moment looks like this:

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]6 Jan 2018[/TD]
[TD="align: right"]5 Jan 2018[/TD]
[TD="align: right"]4 Jan 2018[/TD]
[TD="align: right"]3 Jan 2018[/TD]
[TD="align: right"]2 Jan 2018[/TD]
[TD="align: right"]1 Jan 2018[/TD]
[/TR]
[TR]
[TD]Group1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Location1[/TD]
[/TR]
[TR]
[TD]Group3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Location1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Location2[/TD]
[/TR]
[TR]
[TD]Group6[/TD]
[TD]Location5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Location6[/TD]
[TD][/TD]
[TD]Location3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group8[/TD]
[TD][/TD]
[TD][/TD]
[TD]Location2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The required list looks like this (the column ordering is not important):

[TABLE="class: grid"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]1 Jan 2018[/TD]
[TD]Location1[/TD]
[TD]Group2[/TD]
[/TR]
[TR]
[TD]1 Jan 2018[/TD]
[TD]Location2[/TD]
[TD]Group5[/TD]
[/TR]
[TR]
[TD]2 Jan 2018[/TD]
[TD]Location3[/TD]
[TD]Group7[/TD]
[/TR]
[TR]
[TD]3 Jan 2018[/TD]
[TD]Location1[/TD]
[TD]Group3[/TD]
[/TR]
[TR]
[TD]4 Jan 2018[/TD]
[TD]Location6[/TD]
[TD]Group7[/TD]
[/TR]
[TR]
[TD]4 Jan 2018[/TD]
[TD]Location2[/TD]
[TD]Group8[/TD]
[/TR]
[TR]
[TD]6 Jan 2018[/TD]
[TD]Location5[/TD]
[TD]Group6[/TD]
[/TR]
</tbody>[/TABLE]


I'd have thought I'd be able to find something I could use in the forum, but my searches based on words like "table" and "list" don't seem to bring up anything relevant.

Any help is greatly appreciated!

 

[DRSteele]

Power Query makes quick work of this kind of task. Formulas would be too tough.

Here is the M-code to unpivot that kind of data (using your data as one called Table3). I hope it helps you, but I will include here for everyone's benefit.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"06-Jan-18", type text}, {"05-Jan-18", type any}, {"04-Jan-18", type text}, {"03-Jan-18", type text}, {"02-Jan-18", type text}, {"01-Jan-18", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}, {"Value", "Location"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Location", "Group"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Date", Order.Ascending}, {"Group", Order.Ascending}, {"Location", Order.Ascending}})
in
    #"Sorted Rows"

 

[rpg1966]

Cheers! I have to learn about Power Query and how to run your code first, so apologies for not replying immediately with a "Thanks, that works!", but thanks in advance for when I get it working

 

[DRSteele]

Power Query is powerful. Go to youtube to the ExcelIsFun channel and search through the vids for PQ basics. You will learn at a geometric rate by watching videos. Feed your head.

 

[rpg1966]

Mate, you're a genius. I've got it running after a couple of tiny tweaks to my source data.

The only issue I can see so far is that the date ordering of the results table isn't correct, as there's no year included. My data spans more than one year, so I guess I need to change all the {"9-Jun", type text} entries to {"9-Jun-2018", type text}.

 

[DRSteele]

Right. I believe that In Excel all date-like things should be converted to Excel Date Serial Numbers. Always. Data analysis is so much simpler that way and errors are less likely.

 

[rpg1966]

Ah, I just had to change the formatting of the date row, and all is well. Thank you so much!!

 

[DRSteele]

Good show! I'm glad it worked. You're welcome.

Now, make sure to regularly watch the vids on ExcelIsFun and subscribe to the channel.