Loop over a table to generate open files

[Halmar]

Hi all,

I have a table with end of the month dates.

Date

31-01-22

28-02-22

31-03-22

30-04-22

And I have a table with files and their Startdate and Closedate.

FileID	Startdate	Closedate
1001	01-12-21	01-03-22
2002	15-02-22	02-04-22

I want to create a table with per end of the month date the 'open' files:

Date	Open fileID
31-01-22	1001
28-02-22	1001
28-02-22	2002
31-03-22	2002

I want to do this with a loop.

- Looping over de days

- Filtering the Filetable and check wich Files are open on that date

- Concatenate the results of every loop

Thanks for your help!

Halmar

[Vijay_A_Verma · Accepted solution]

See the working here for Table2 (it refers to first table as Table1) - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcnBDQAgCATBXu5NwnFqNYT+2xCDv81sJoIMGEIeLur1mhTKEiLV1vsMGrbr/7o=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileID = _t, Startdate = _t, Closedate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FileID", Int64.Type}, {"Startdate", type date}, {"Closedate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Open fileID", each Table.SelectRows(Table1, (x)=> x[Date]>=[Startdate] and x[Date]<=[Closedate])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Startdate", "Closedate"}),
    #"Expanded Open fileID" = Table.ExpandTableColumn(#"Removed Columns", "Open fileID", {"Date"}, {"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Open fileID",{"Date", "FileID"})
in
    #"Reordered Columns"

 If you need code for Table1 as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NtQ3MjAyUorViVYy0jeyQPCMUeRM9I0NoLxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"