Forum Discussion
Data loading into Power bi
Hi Community,
I have 3 files ( 2 files -2014.csv,2015.csv historical files, 1 file- current day file). I have to apply below logic.
- Keep all the records from the Current File for employee.
- Merge in only those records from the Historical File for Employee that have a Proj-No that is not found in the Current File for employee.
Please help.
Thanks,
Paruchuri
3 Replies
We can do this. Firstly create a super historic table. This being where you combine all of your historic files into a single table. Im going to assume your 2014 and 2015 csv files are in the same format. You can simply use the "Append Queries" to do this.
Now that you have a super table, you need to make sure your Super Historic table and your Current table have some unique value set that we can join with. I'm expecting this will be a combination of Employee and Project Number.
You may at this point need to remove any duplications in your super table, such that your combined key is unique.
Now do a Join from the Super Historic table with your Current table. Make it a 'Left Outer Join' where your Super Historic table is the left table (Table 1).
Expand out your columns and then filter so you only keep the ones with null records.
BOOM, you have the lines you want.
Hi,
The 3 files i have are having same format data.
Now do a Join from the Super Historic table with your Current table. Make it a 'Left Outer Join' where your Super Historic table is the left table (Table 1).
I need to keep current file records and only matched records from historical files, I think current table is on left.
Thanks,
Paruchuri
Once you have the end result, you will have a Table of just the missing lines and a table of the current lines. You would do an append operation to bring them together. Do this in the table containing the missing lines. You can then uncheck "enable load" on the other tables and just keep this table.