Considering Specific Days Between Two Dates

[Ryukatan10]

Hello everyone, I'm stuck with this issue and don't know how to solve it.

I have a database that show several rows like this one:

Flight	Acft	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Begin Operation	End Operation
5467	C208	0	0	0	0	0	0	7	05/01/2022	06/27/2022

In this case, I have a flight registration that will ocurr from 05/01/2022 to 06/27/2022, only at Sunday, because all the others weekday are "0" in this row.

I would like that Power BI shows or undertand something like that:

Flight	Date
5467	05/01/2022
5467	05/08/2022
5467	05/15/2022
5467	05/22/2022
5467	05/29/2022
5467	06/05/2022
5467	06/12/2022
5467	06/19/2022
5467	06/26/2022

I already have another table with dates in one column and weekday in number from 1 to 7 in other column, but even if I create a relationship with al the seven columns form the flights database with this table of dates, it not work in the way that i want.

Can Anyone Help Me?

[Ryukatan10]

@tackytechtom Perfect Man

I had to make some changes but the core problem was solved with your solution, thanks a lot

[tackytechtom · Accepted solution]

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/