Forum Discussion
Transforming schedule data
Ok, that was a pretty simple change. Rather than adding 6 days, I just went from start to end.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUN9Q3MjAyAjONYUwDJGyiZwokDeEisTrRShDlhgYw9YZG+sZwcwyxYIRWY4hWI4StRnBrjcBWGWNhQfXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start_Date = _t, End_Date = _t, Monday_Hours = _t, Tuesday_Hours = _t, Wednesday_Hours = _t, Thursday_Hours = _t, Friday_Hours = _t, Saturday_Hours = _t, Sunday_Hours = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start_Date", "End_Date"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Start_Date", type date}, {"End_Date", type date}, {"Value", Currency.Type}}), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}), #"Filtered out Zeros" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> 0)), #"Duplicated Column" = Table.DuplicateColumn(#"Filtered out Zeros", "Start_Date", "Date Bracket"), AddDateRange = Table.AddColumn( #"Duplicated Column", "Date Range", each let varStartDate = Number.From([Start_Date]), varEndDate = Number.From([End_Date]) in {varStartDate..varEndDate} ), #"Expanded Date Range" = Table.ExpandListColumn(AddDateRange, "Date Range"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}), #"Inserted Day Name" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Date Range]), type text), #"Added Match" = Table.AddColumn(#"Inserted Day Name", "Match", each [Attribute] = [Day Name], Logical.Type), #"Filtered for Match is TRUE" = Table.SelectRows(#"Added Match", each ([Match] = true)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered for Match is TRUE",{"ID", "Date Range", "Value"}), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ID", "Date Range"}, {{"Hours", each List.Sum([Value]), type nullable number}}) in #"Grouped Rows"So just for ID 3, I get this. Originally 3 was 5 rows, but spanned 2 weeks, so it makes sense to me it now has 10 rows. As you said, ID 2 is dozens of rows...
Hi edhans ,
Thanks for your reply. That gets me close, however for the rows in the original table that span multiple weeks I need to be able to reflect all those dates in the final solution. For example ID 003 in the original table has a start date of 9/12/2022 and an end date of 9/23/2022. This spans two weeks, so the pivot would need to result in 12 dates (excluding zeros). Essentially in that example, there would be a 9/12/2022 of 2.5 hours and also a 9/19/2022 of 2.5 hours. ID 002 is a much larger range going all the way to the end of the year, so it would encompass all dates in that range. Basically the hours for each weekday would need a coresponding row for every occurance of that weekday during the date range.
Hopefully that makes sense and adds neccessary context?
Thanks so much for your help!
Ok, that was a pretty simple change. Rather than adding 6 days, I just went from start to end.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUN9Q3MjAyAjONYUwDJGyiZwokDeEisTrRShDlhgYw9YZG+sZwcwyxYIRWY4hWI4StRnBrjcBWGWNhQfXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start_Date = _t, End_Date = _t, Monday_Hours = _t, Tuesday_Hours = _t, Wednesday_Hours = _t, Thursday_Hours = _t, Friday_Hours = _t, Saturday_Hours = _t, Sunday_Hours = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start_Date", "End_Date"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Start_Date", type date}, {"End_Date", type date}, {"Value", Currency.Type}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Filtered out Zeros" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> 0)),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered out Zeros", "Start_Date", "Date Bracket"),
AddDateRange =
Table.AddColumn(
#"Duplicated Column",
"Date Range",
each
let
varStartDate = Number.From([Start_Date]),
varEndDate = Number.From([End_Date])
in
{varStartDate..varEndDate}
),
#"Expanded Date Range" = Table.ExpandListColumn(AddDateRange, "Date Range"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
#"Inserted Day Name" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
#"Added Match" = Table.AddColumn(#"Inserted Day Name", "Match", each [Attribute] = [Day Name], Logical.Type),
#"Filtered for Match is TRUE" = Table.SelectRows(#"Added Match", each ([Match] = true)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered for Match is TRUE",{"ID", "Date Range", "Value"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ID", "Date Range"}, {{"Hours", each List.Sum([Value]), type nullable number}})
in
#"Grouped Rows"So just for ID 3, I get this. Originally 3 was 5 rows, but spanned 2 weeks, so it makes sense to me it now has 10 rows. As you said, ID 2 is dozens of rows...
edhans thanks so much! This is great! I did some quick validation on my dataset and everything looks good. I greatly appreciate your time and explaining the steps you took to get to the solution.
AlexisOlson Thanks for your insight as well. My dataset is small enough right now that the run time is not of concern, but this is good to know so I can build my report to scale.
rbreneman It looks like edhans nailed it.
I just wanted to add that if your dataset is large, then you may want to filter for the day of the week before expanding rather than after so it doesn't need to expand into as many rows. It makes the AddDateRange step more complex though, so it might not be worth the extra effort if your data is small (though it does eliminate the need for a few of the later steps).
AddDateRange = Table.AddColumn( #"Filtered out Zeros", "Date Range", (r) => let Days = {Number.From(r[Start_Date])..Number.From(r[End_Date])}, Dates = List.Transform(Days, Date.From), Filtered = List.Select(Dates, each Date.DayOfWeekName(_) = r[Attribute]) in Filtered, type list )
