Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 | Departm...
  • Syk's avatar
    Syk
    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 this

    8.) add a slicer on the type

    Hope this helps!