Forum Discussion
Transforming schedule data
- 3 years ago
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...
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.