Forum Discussion
Remodel a table by calculating time difference
Hello,
I have table 1 with Xvalue+ date when collected and table 2 with various Yvalues + date when been collected. I want to get a third table with a list of all single dates (+ Yvalue) that is the closest in time with the date of X value. The time difference date(X) - date(Y) should be of 45 days or lower.
The result should look like this:
I have 150 ids with over 30 dates each and I would like to find a quick way to filter it in a clear and neat way.
Thank you in advance!
4 Replies
With Power Query you can add data to the blue dynamic tables (table1 and table2). Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. The green result table can stay to the right of the blue tables or it can be placed in another worksheet.
- =DROP(REDUCE(Table_1[#Headers],SEQUENCE(ROWS(Table_1[date])),LAMBDA(x,y,
LET(z,WRAPROWS(CHOOSEROWS(Table_2[[date]:[Y4]],y),2),u,BYROW(CHOOSECOLS(z,1),LAMBDA(r,ABS(INDEX(Table_1[date],y)-r))),VSTACK(x,HSTACK(y,XLOOKUP(MIN(u),u,z)))))),1)An alternative could be this formula which returns the intended result in Excel for the web or Office 365.
OliverScheurichthank you for you answer!
I have tried multiple times but get each time this message of error. Do you know what the cause could be?
From the screenshot i can't tell why it doesn't work. You can open my attached file and the formula is translated into your language and the formula returns the intended result. This time i've used your tablenames Tabell1 and Tabell2.
