Forum Discussion

rbreneman's avatar
rbreneman
Helper II
3 years ago
Solved

Transforming schedule data

Hi!   I could use some help on trying to transform data in Power Query Editor. I have data that is structed as shown below. As you can see the start and end dates could be a rather large range, not...
  • edhans's avatar
    edhans
    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...