Forum Discussion
merging tables on two columns
did you guys solve it with SQL after all?
If so, could I ask you to accept a solution for this post, so others can benefit from it anyway?
Anonymous
So that would work if the configuration table had an entry for every month that I had data. Right now, it doesn't, it only logs the changes. Using a [unit]&[Date.Month]&[Date.Year] key would enable this simply enough, if I had an entry for each and every month in both tables. Using that approach right now yields a huge number of null rows in the subsequent merge.
Maybe I misunderstand your reply, in which case please correct me. But you could probably duplicate configuration table for each month? It's just a new duplication when you guys have changes in the configuration table?
An example: Check whether there is any changes to configuration table: If there is none, duplicate last month, if there is changes duplicate those changes. Wouldn't this accommodate your problem?
did you guys solve it with SQL after all?
If so, could I ask you to accept a solution for this post, so others can benefit from it anyway?
Yeah, we did it at the DB level in the end. Thanks for the help!
Changing the source data set is harder to do than it should be. Would it be possible to duplicate those rows in a table within the analysis?
Hmm. I guess you could do it in Power Query. Maybe it's also possible with DAX, I honestly don't know. But going forward I would suggest taking this approach.
Our data team has done this in SQL Server for us as a proof of concept, so I get the end resulting table directly on SQL Server and can do my visualisation on the back of that, as opposed to trying to construct it here. Thanks for the suggestions!
I'm glad you guys have tried it out, and that it works for you. If you consider this post solved, could I ask you accept a solution?
Best of luck in the future,
Martin