Forum Discussion
Loading selective months from previous data files
Hi,
I have a dataset with the Month column which contains data from Jun to Dec 2020. In another file sent, I have the dataset with the same set of columns but for months Apr to Dec 2020 i.e. it contains 2 months extra data for Apr and May as compared to the first file.
File 1 (latest): Jun to Dec 2020 data
File 2: Apr to Dec 2020 data
File 3: Jan to Mar 2020 data
I want to dynamically set up the data load in the Query Editor wherein the latest file i.e. File 1 is loaded completely and in the previous files i.e. File 2 and File 3, only the extra months data is loaded.
In above scenario, I would first like the data from File 1 fully loaded from Jun to Dec 2020.
Then, bring in data from File 2 and only load Apr and May 2020 as File 1 already contains the most recent and updated data from Jun to Dec 2020.
Last, bring in File 3 data but load the complete data as Jan to Mar data is not available previously.
I want to set this up dynamically in the Query Editor. Please note that there is no fixed frequency of producing these data files nor is there any consistency in the number of months of data contained in the file. I understand I can manually do it and remove the months I do not need in the Query Editor but the requirement is to dynamically set this up and possible automate it.
Is this possible in the Query Editor or any other way in Power BI? Please let me know with a sample pbix or so which would be helpful. Thanks.
Regards,
Vishy
Anonymous solution attached, you can further tweak it as per your need, but I guess this will give you the basic idea.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
12 Replies
Anonymous it can be easily achieved using table merge and append, I don't see this an issue.
table 1 -> File 1 (latest): Jun to Dec 2020 data
table 2 -> File 2: Apr to Dec 2020 data
table 3 -> File 3: Jan to Mar 2020 data
1- create new merge table using where table 2 contains rows which are not in table 1 -> let's call it to merge table 1 and 2
2- now create another table combine of table 1 and above table from step 1, basically, this table has a unique months data from table 1 and table2
3- create another merge table from table 3 and table from step 2, get only new months from this table 3
4- create a final output table by combining tables of step 2 and step 3
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hi parry2k ,
I did not understand the point where you mentioned 1- create new merge table using where table 2 contains rows which are not in table 1. In my case, the file has the same set of columns in the same order so I guess Append would be the appropriate command and not Merge. I did not see any option in Append to remove the unwanted rows. Can you help me to understand this better?
Also, the requirement is to automate this as these files would be placed in a folder so I was checking if there is any way to automate the loading of rows based on the latest file that would be loaded first. Will parameters or so help in this case?
If we can't automate this, would atleast like to know the manual options that are available.
Thanks,
Vishy
Hi Anonymous ,
Please take a look at the following blog and check if it can help solve your problem:
Combining Data From Multiple Worksheets In The Same Excel Workbook Using Power BI
Best Regards
Rena
Hi Anonymous ,
The blog shared is for transformations to be done on each file that is uploaded from a folder etc.
In my case, I do not want any transformation done on the latest file because I want all the rows and columns from that file as it contains the latest data.
All subsequent older files that are loaded, I want selective rows to be uploaded based on the latest file i.e. if a certain month rows are not part of the latest file, I want them uploaded, else not.And as I mentioned previously, I wanted to see if this can be automated without manual intervention, seems it can't.
We can ofcourse do it manually by removing rows that are not needed but the first preference was to automate.
Correct me if I am wrong in my understanding.
Thanks,
Vishy
Anonymous send me pbix file with sample data and I will put together the solution for you otherwise it is bit hard to explain.