Unsure How to Model for this Need (Months with different values)

[Brenden]

I have a dataset that looks like this simplified version below:
Forecast | Department | Volume January | Volume February | Handle Time January | Handle Time February | ..and so on

Actuals | Department | Volume January | Volume February | Handle Time January | Handle Time February | ..and so on

I want to be able to make a table or Matrix that looks like this, and be able to be filtered by Forecast/Actuals in a slicer. Volume and Handle Times should be stacked by their months:
                      | January | February  |

Volume         |   123     |     456       |

Handle Time|   789     |     012       |

Since the Volume and Handle Time values are stored in separate columns, I am not sure what I need to do to get them to be stackable. I have tried pivot but I am lost on if this is the correct approach / how to do it properly.

[Syk]

Your volume and handle time should be rows with your date as well. Is 'Forecast' and 'Actuals' column names? I'd recommend they also be in your row as a 'type' column.

If you can provide a sample of some of your data, I can help you with how to pivot!

[Brenden]

I agree - volume and handle time should be rows, but unfortunately they are not, in the data I have received 😩

Forecast and Actuals are values under a Type column (sorry, my mistake not clarifying it in the original post)

Here is a sample:

Type	Department	vol_jan	vol_feb	vol_mar	vol_apr	vol_may	vol_jun	vol_jul	vol_aug	vol_sep	vol_oct	vol_nov	vol_dec	vol_total	aht_jan	aht_feb	aht_mar	aht_apr	aht_may	aht_jun	aht_jul	aht_aug	aht_sep	aht_oct	aht_nov	aht_dec	aht_total
Forecast	Internal	168	167	166	166	166	165	165	165	165	164	163	163	1983	296	296	296	296	296	296	296	296	296	296	296	296	296
Actual	Internal	173	68	63	116	83	119								217	106	110	154	247	191
Forecast	Internal	435	433	431	430	430	429	429	428	427	425	424	422	5142	790	790	790	790	790	790	790	790	790	790	790	790	790
Actual	Internal	332	225	492	485	298	144								657	632	837	899	1209	1119
Forecast	Internal	8	8	7	7	7	7	6	6	5	5	5	4	75	760	760	760	760	760	760	760	760	760	760	760	760	760
Actual	Internal	12	21	1	2										1360	1246	262	458

[Syk · Accepted solution]

I'll tell you what I did and copy the M code here for reference. The source is me just copy/pasting the data you provided.

1) Remove the total columns, they will mess you up later and not needed

2) Select your Type and Department columns and "Unpivot Other Columns" (under transform tab)

3) Split your column to make sure your volume/aht column is separate from your month. In the data provided you can split on the underscore delimiter.
4) (optional) Create a new column to spell out the full name of the month. I used the columns from example option to do this.
5) Rename all your columns to what they represent and remove the abbreviated month column!

Should look something like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZK9DoJQDIVfxTAz3Pb2/o0uJj4DYSCEzWCC+P7SAwaiAg4M52tDUtqe26LILveuqatHn+XZte2brq1uQ0o+ggH0X3QrFNDOTFEDJ38Yy7zIznX/xJzLkYN2wtxja9KKOKZp4GlDTNjUYDsySqe7sOB7os16Hem3j2IdaEECzUxOC0YwgKhiAXmgI9EQkjmMaz5aq514HCFpLtHBe5yEyI6V3gU8AcN9zWPSBYkNwt5jrJsZJ4UP+UluIZ0yaBK8OYyrpwfH9HVVvGPQW2TxY2LBaXtY7eJf1WX5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Department = _t, vol_jan = _t, vol_feb = _t, vol_mar = _t, vol_apr = _t, vol_may = _t, vol_jun = _t, vol_jul = _t, vol_aug = _t, vol_sep = _t, vol_oct = _t, vol_nov = _t, vol_dec = _t, vol_total = _t, aht_jan = _t, aht_feb = _t, aht_mar = _t, aht_apr = _t, aht_may = _t, aht_jun = _t, aht_jul = _t, aht_aug = _t, aht_sep = _t, aht_oct = _t, aht_nov = _t, aht_dec = _t, aht_total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Department", type text}, {"vol_jan", Int64.Type}, {"vol_feb", Int64.Type}, {"vol_mar", Int64.Type}, {"vol_apr", Int64.Type}, {"vol_may", Int64.Type}, {"vol_jun", Int64.Type}, {"vol_jul", Int64.Type}, {"vol_aug", Int64.Type}, {"vol_sep", Int64.Type}, {"vol_oct", Int64.Type}, {"vol_nov", Int64.Type}, {"vol_dec", Int64.Type}, {"vol_total", Int64.Type}, {"aht_jan", Int64.Type}, {"aht_feb", Int64.Type}, {"aht_mar", Int64.Type}, {"aht_apr", Int64.Type}, {"aht_may", Int64.Type}, {"aht_jun", Int64.Type}, {"aht_jul", Int64.Type}, {"aht_aug", Int64.Type}, {"aht_sep", Int64.Type}, {"aht_oct", Int64.Type}, {"aht_nov", Int64.Type}, {"aht_dec", Int64.Type}, {"aht_total", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"vol_total", "aht_total"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Type", "Department"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if [Attribute.2] = "jan" then "January" else if [Attribute.2] = "feb" then "February" else if [Attribute.2] = "mar" then "March" else if [Attribute.2] = "apr" then "April" else if [Attribute.2] = "may" then "May" else if [Attribute.2] = "jun" then "June" else if [Attribute.2] = "jul" then "July" else if [Attribute.2] = "aug" then "August" else if [Attribute.2] = "sep" then "September" else if [Attribute.2] = "oct" then "Octobre" else if [Attribute.2] = "nov" then "November" else if [Attribute.2] = "dec" then "December" else null, type text),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Month"}, {"Attribute.1", "Value Type"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.2"})
in
    #"Removed Columns1"

6) close and apply
7) Create a matrix and fill out like this

8.) add a slicer on the type

Hope this helps!

[Brenden]

You are a life saver! Really appreciate the help. It does what I need 🙂

[Syk]

One more thing.. You can create a month number column the same way we did the month column. Once you do that and apply it, select the month column > column tools > sort by column> and choose month number.

This will make sure its Jan-> December instead of April->Sept (alphabetical)