merging tables on two columns

[pxg638]

Hi there, 

First post, so apologies if this is super dumb - I was either searching for the wrong thing or being exceedingly dense in understanding what others had written! I have a large (1m row) data table and a small (few hundred row) configuration table and I am trying to merge them together, thus:

Table.Data

Date Monthly Production Unit

31/1/2017	1000	CNC1
31/1/2017	2000	CNC2
31/1/2017	3000	CNC3
28/2/2017	1000	CNC1
28/2/2017	2000	CNC2
28/2/2017	3000	CNC3

etc

Table.Configuration

Date Unit Type1Jobs Type2Jobs

1/1/2017	CNC1	0.5	0.5
1/2/2017	CNC1	0.6	0.4
1/1/2017	CNC2	0.5	0.5
1/1/2017	CNC3	0.5	0.5
1/2/2017	CNC3	0	1

In other words, for the month of Jan, CNC1 did 50% of Type1 jobs and 50% of type 2 jobs, and then in Feb was re-configured to do 60% type 1 jobs and 40% type 2 jobs.

The outcome I want is effectively:

Date Monthly Production Unit Type1Work Type2Work

31/1/2017	1000	CNC1	500	500
31/1/2017	2000	CNC2	1000	1000
31/1/2017	3000	CNC3	1500	1500
28/2/2017	1000	CNC1	600	400
28/2/2017	2000	CNC2	1000	1000
28/2/2017	3000	CNC3	0	3000

So the monthly production is shared out based on the fraction of the type of work, per machine. The end result I want to see is type of work over time per machine, as those get grouped by site.

Now, I can do a simple left outer join to achieve a baseline configuration, but I cannot do a join on two columns without getting into between/range type thinking and that is beyond my brain at this time, and therefore any configuration change of the machine is lost.

I see answers sort of suggesting a 2nd date table somehow, but I am struggling to see the solution. I am sure this is super simple, what am I missing?

[vanessafvg]

@pxg638 why dont you create a new column, ie a key based on the combination of  both the columns?

[pxg638]

So I can create [Unit]&[Date] in both tables, that is simple enough, how would I then utilise that to create the change in allocation between jobtypes? Wouldn't a join on the new column simply leave lots of gaps where it didn't key up?

[vanessafvg]

@pxg638 ah ok, tell me a bit more about the change in allocation, its not clear to me from your post (i might be a bit slow this morning)

[pxg638]

So for January we do 1000 jobs. The unit is configured to do 50% type 1 and 50% type 2, so that gets 500 of the first, 500 of the second. The next month we also do 1000 jobs, but we have reconfigured the machine, so it now does 40% type 1 and 60% type 2, so I get 400 and 600 respectively. If I want to now sum my type 1 jobs, I'd like to know this unit did 900 type 1s and 1100 type 2s in the first two months. 

That any clearer?

[Anonymous]

Hi @pxg638

Could you provide column headers? I want to re-create your problem in Power BI to test a solution, I have in mind.

Thanks in advance.

Best

Martin 

[pxg638]

@Anonymous

Column headers failed to show on the original post, sorry! HTML failed me! So you have reporting date, volume, and unit for the first table, then configuration date, unitname, fraction T1 work and fraction T2 work (effectively) for the 2nd table.

I am simplying the reality here, but the principle is presented.

[Anonymous]

 @pxg638

Alright, the way, I've solved this, is by generating an ID-column for linking a certain production to a certain configuration. I'm not sure whether this will accommodate your dataflow or data-setup. If you can't generate an ID-column, I suggest you tell us a little more about your challenge and data-setup, so I can figure out a suiting solution for you. The smoothest way, in my opinion, to go about your challenge is to generate an ID-column. Take a look a below picture.

Let me know if this solves your problem, and if we should go in a different direction.

[pxg638]

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

[Anonymous]

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?

[pxg638]

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?

[Anonymous]

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.

[pxg638]

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!

[Anonymous]

@pxg638

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

[Anonymous · Accepted solution]

@pxg638

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?

[pxg638]

Yeah, we did it at the DB level in the end. Thanks for the help!

[Anonymous]

The example is just fine. I had missed the headers, so thank you for providing them. I'll re-create this in Power BI now, and try out my solution. Then I'll get back to you with the results.

Best

Martin