Considering Specific Days Between Two Dates

Hi Ryukatan10 ,

Here a possible solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUxM1fSUXI2MrAAUgY4MEiJgaG+gam+kYGREZBjZK5vYAbhxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Flight = _t, Acft = _t, Mon = _t, Tue = _t, Wed = _t, Thu = _t, Fri = _t, Sat = _t, Sun = _t, #"Begin Operation" = _t, #"End Operation" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Flight", Int64.Type}, {"Acft", type text}, {"Mon", Int64.Type}, {"Tue", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}, {"Begin Operation", type date}, {"End Operation", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Flight", "Acft", "Begin Operation", "End Operation"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] <> 0),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each { Number.From([Begin Operation])..Number.From([End Operation]) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if Text.Start ( Date.DayOfWeekName([Custom], "en-US"), 3) = [Attribute] then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Acft", "Begin Operation", "End Operation", "Attribute", "Value", "Custom.1"})
in
    #"Removed Columns"

I did even an unpivoting on your day columns which was not necessarily needed to fix the example above. However, I hope with that it even works with other rows in your data. I.e. with flights that occurr on multiple days in a week etc. Not sure, whether this one here will be the final solution, but I think we got a step closer.

Let me know if it works out and come back if you have questions 🙂 Also, feel free to share some more data if you need more help.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/