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!
- Anonymous3 years agoNot applicable
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 🙂