Loading selective months from previous data files

[vishy86]

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

[v-yiruan-msft]

Hi @vishy86 ,

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

[vishy86]

Hi @v-yiruan-msft ,

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

[parry2k]

@vishy86 send me pbix file with sample data and I will put together the solution for you otherwise it is bit hard to explain.

[vishy86]

Hi @parry2k ,

Sure, let me try and get some sample data and files together, will send it across and explain the desired outcome.

Regards,

Vishy

[vishy86]

Hi @parry2k ,

Please find the pbix file with sample data loaded in the link below:

https://drive.google.com/file/d/1BfVAVJs_heORrOhac6sH7RCBEKZ2b_LP/view?usp=sharing

There are 3 data files loaded:

1. Sample Report Jun to Dec (latest file)

2. Sample Report Apr to Dec

3. Sample Report Jan to Mar

The desired outcome is to setup a logic wherein all the rows from the latest file get loaded into the Power BI Model.

When File 2. gets loaded, only rows from Apr and May should be loaded into the Power BI Model as the latest file already contains rows from Jun to Dec.

When File 3. gets loaded, all the rows from Jan to Mar should get loaded as the Power BI Data dataset contains rows from Apr to Dec now so there is no overlap in this case.

Please let me know if you have any queries.

Thanks,

Vishy

[parry2k]

@vishy86 I cannot perform any power query steps, I need these 3 excel files as well or you should create data table directly in the power bi rather pointing to excel file.

[parry2k · Accepted solution]

@vishy86 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.⚡

[vishy86]

Thank you @parry2k for the help !

[vishy86]

Hi @parry2k ,

Just 1 follow-up question. The steps given by you surely work and achieve the result.

But given the scenario that the file names would change every month as this data is generated monthly or sometimes even weekly, my understanding is that there needs to be a fixed standard file name such as Current Month, Previous Month and so on so that the Merge and Append operations point to the same dataset name so as to ensure there is minimal to no manual intervention needed.

Is that correct? Please advise.

Regards,

Vishy

[vishy86]

Hi @parry2k ,

Please find the data files uploaded in the below link - 

https://drive.google.com/file/d/1Rdrw84QdH8ktIlmRaD5eloFmzwy5yu42/view?usp=sharing

Regards,

Vishy

[parry2k]

@vishy86 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.⚡

[vishy86]

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