Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

1 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)

Syk_0-1689267491379.png

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.

Syk_2-1689267907184.png

 



 

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

Syk_1-1689267829323.png

8.) add a slicer on the type

Hope this helps!



View solution in original post

5 REPLIES 5
Syk
Super User
Super User

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

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)

Syk_0-1689267491379.png

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.

Syk_2-1689267907184.png

 



 

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

Syk_1-1689267829323.png

8.) add a slicer on the type

Hope this helps!



Anonymous
Not applicable

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

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.

Syk_0-1689268364399.png

 


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors