I did this successfully but am wondering if there is an easier way to do it for ongoing tracking? I am measuring the results of an auto promotion. I have a table of all auto sales (for a period) which is my primary table. The other table is a loan table of autos financed by us. These tables are not related but they do have a ‘VIN’ field that will match and help identify those of our customers that purchased (and financed) a car.
With that setup, I used the ‘Merge’ function (on VIN) with an inner join and created a 3rd table that effectively are just my customers that purchased and financed with us (the results of the promotion). I created several measures and a small dashboard to show the results (from these 3 unrelated tables).
This worked fine. I’m now trying to do this the second month and wondering if there is an easier way that might make it possible to ‘refresh’ and get the results. I’m not aware of a way to automate the Merge function and that is making the process more manual than I’d like. Is there another way to do this that I’m not considering?
You want to aim to have everything happen automatically, that includes the merging of tables which can be done in the query editor.
When you refresh your data you want Power BI to reach back, grab the data, then every change and transformation just flow naturally after that so all you tables have the same structure and then all your formulas work as they should.
I’m finding it difficult to fully understand every aspect of the scenario here, but you want to aim to have lookup tables and fact tables and have a clear delineation between the two.
From my perspective looking in here it’s not clear at the moment.
For your lookup tables you want table like customers, loan types, dates etc etc. Clear groupings of related dimensions.
Then your fact tables should be things like Sales, Loans. Something like this.
To me it’s doesn’t look like the model is setup in a way to succeed just yet.
All my best practices are covered in this module in detail. Highly recommend working through it if you haven’t already.
Okay, thanks. I have the Merge set up in the query editor but it is not updating the merge file when I refresh the new month’s data files so I wasn’t sure that was something that could happen in the QE. Knowing that it is, is helpful and it is probably something I did wrong in the process.
In reviewing some of your other videos, I ended up using another method and created a virtual table with the Intersect function and it seems to be working and giving me the right data. I clearly don’t understand the limits of a virtual table because I thought that I’d need a real table for my use. It is working, however, so I’ll go with that. It is definitely the more simple method.
Just a brief note. Regarding updating new months from the query. If you use get from FOLDER instead of a specific file… and the files are set up in the same way, you can easily create a setup that updates automatically.
