Transforming schedule data

rbreneman here is what I came up with:

Here is my code:

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])
                in
                {varStartDate..varStartDate+6}
        ),
    #"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"})
in
    #"Removed Other Columns"

Here is what I did:

1. Unpivoted all but the ID, StartDate and End Date. That gives me a clean table.
2. The Attribute column now has your "Monday_Hours" field name in it. I got rid of the _Hours part.
3. Filtered out 0 rows for the value.
4. Now I added a date range based on the Start Date Plus 6 days, so a full week. This is a nested list in the "AddDateRange" step.
5. Expanded that and changed the Date Range to a date data type.
6. I inserted the name of the day based on that date range.
7. I then compared that day name to your Attribute (your old column) and only kept those that matched.
8. Removed all of the unnecessary columns.

 

If this isn't what you need, please be more explicit in how you expect to arrive at the results. Your example wasn't 100% clear to me, but my solution makes sense. It may not be right for your needs though.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.