Forum Discussion
Unsure How to Model for this Need (Months with different values)
- 3 years ago
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 this8.) add a slicer on the type
Hope this helps!
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!
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 |
- Syk3 years ago
Resident Rockstar
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 this8.) add a slicer on the type
Hope this helps!- Anonymous3 years agoNot applicable
You are a life saver! Really appreciate the help. It does what I need 🙂
- Syk3 years ago
Resident Rockstar
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)