Forum Discussion
Loop over a table to generate open files
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
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"
1 Reply
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"