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:

	6 Jan 2018	5 Jan 2018	4 Jan 2018	3 Jan 2018	2 Jan 2018	1 Jan 2018
Group1
Group2						Location1
Group3				Location1
Group4
Group5						Location2
Group6	Location5
Group7			Location6		Location3
Group8			Location2
Group9

<tbody>
</tbody>

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

Date	Location	Group
1 Jan 2018	Location1	Group2
1 Jan 2018	Location2	Group5
2 Jan 2018	Location3	Group7
3 Jan 2018	Location1	Group3
4 Jan 2018	Location6	Group7
4 Jan 2018	Location2	Group8
6 Jan 2018	Location5	Group6

<tbody>
</tbody>

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.