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 | 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'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!



5 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar

    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!

    • Anonymous's avatar
      Anonymous
      Not 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:

      TypeDepartmentvol_janvol_febvol_marvol_aprvol_mayvol_junvol_julvol_augvol_sepvol_octvol_novvol_decvol_totalaht_janaht_febaht_maraht_apraht_mayaht_junaht_julaht_augaht_sepaht_octaht_novaht_decaht_total
      ForecastInternal1681671661661661651651651651641631631983296296296296296296296296296296296296296
      ActualInternal173686311683119       217106110154247191       
      ForecastInternal4354334314304304294294284274254244225142790790790790790790790790790790790790790
      ActualInternal332225492485298144       65763283789912091119       
      ForecastInternal88777766555475760760760760760760760760760760760760760
      ActualInternal122112         13601246262458         
      • Syk's avatar
        Syk
        Icon for Resident Rockstar rankResident 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 this

        8.) add a slicer on the type

        Hope this helps!