Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello! I am trying to either unpivot or pivot my data from the top format into the bottom format in order to calculate percent difference week over week. Thank you!
Sure thing
Week Of | day_abbrev | Service Level |
9/4/2022 | Mon | 0.98 |
9/4/2022 | Tues | 0.86 |
9/4/2022 | Wed | 0.85 |
9/4/2022 | Thurs | 0.84 |
9/4/2022 | Fri | 0.77 |
9/25/2022 | Mon | 0.51 |
9/25/2022 | Tues | 0.94 |
9/25/2022 | Wed | 0.91 |
9/25/2022 | Thurs | 0.91 |
9/25/2022 | Fri | 0.92 |
9/18/2022 | Mon | 0.64 |
9/18/2022 | Tues | 0.86 |
9/18/2022 | Wed | 0.77 |
9/18/2022 | Thurs | 0.9 |
9/18/2022 | Fri | 0.7 |
Hopefully I've understood what you want, as you do not show the desired results based on the source data.
I assume you want the percent change for each weekdays Service Level.
Read the comments and explore the Applied Steps to understand the algorithm:
Original Data
let
//change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
//set correct data types
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Week Of", type date},
{"day_abbrev", type text},
{"Service Level", type number}
}),
//Sort by Week Of, Descending
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Week Of", Order.Descending}}),
//Group by Weekday
// Generate List of percent changes based on week to week change
// Return Week Of Date and Percent change columns
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"day_abbrev"}, {
{"Change", (t)=>
Table.FromColumns({t[Week Of]} &
{List.Generate(
()=>[d=(t[Service Level]{0} - t[Service Level]{1}) / t[Service Level]{1}, idx=0],
each [idx] < (Table.RowCount(t)),
each [d=try (t[Service Level]{[idx]+1} - t[Service Level]{[idx]+2}) / t[Service Level]{[idx]+2} otherwise null, idx=[idx]+1],
each [d])}),
type table[Column1=date, Column2=Percentage.Type]}
}),
#"Expanded Percent Change" = Table.ExpandTableColumn(#"Grouped Rows", "Change",
{"Column1", "Column2"},{"Week Of", "Percent Change"}),
//Pivot on Week Of, with no aggregaton
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Percent Change",
{{"Week Of", type text}}, "en-US"),
List.Distinct(Table.TransformColumnTypes(#"Expanded Percent Change",
{{"Week Of", type text}}, "en-US")[#"Week Of"]), "Week Of", "Percent Change"),
//add day number column for sorting
// then sort by day number and remove that column
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "day number",
each List.PositionOf({"Mon","Tues","Wed","Thurs","Fri"},[day_abbrev])),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"day number", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"day number"})
in
#"Removed Columns"
Results
It would be helpful if you posted your source data as text which can be copy/pasted, rather than as a screenshot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.