The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I need help trying to bring back values from one table to another based on dates.
I have a table with staff with different effect date values for employment status like someone changing Permanent to contractor etc.
I am needing the values from table 1 in table 2 based on each staff and their effective dates.
Please see the link below to the sample data and my expected outcome.
Sample Data and Expected Outcome
Hi @rush ,
In Power Query, you can get the value from another table by doing a merge. If there are multiple criteria, you can add a column in each of the table which combines all the needed columns as in your case the effective date and EEID and then use that column to merge. I am however confused with your expected result because, for example, 122 and 02/02/2021 does not exist in Table1 but is in your expected outcome.
Please refer to this pbix for the initial solution. You will find it when you go to the Query Editor. https://drive.google.com/file/d/1bNgZfgo4Fhd0mnJ3BvKR_dCrD1Mqigtz/view?usp=sharing
@danextian Thank you for your response.
So it is not just a match on the date only but it needs to fill it up until there is another effective date.
I can't use power query as I have table 2 created using DAX.
It needs to fill in the data from employment status based on the effective date. So if there are 2 or more effective dates it will fill the table with those corresponding dates.
User | Count |
---|---|
79 | |
78 | |
37 | |
33 | |
31 |
User | Count |
---|---|
93 | |
81 | |
59 | |
49 | |
48 |