Creating a new table from two existing ones

[ruhor]

I have two tables that I am pulling from our database. One table is called utilityhistory and the other utilitymeters. I have posted the actual tables below:

utilityhistory

utilid	meterid	propid	unit	currread	currdate	prevread	prevdate	consumption	adjustedconsumption	amount	postdate
142	154     146	001	395920	        1/8/2016 0:00	391860	        12/14/2015 0:00	 4060	         4060	                24.28	2/1/2016 0:00
143	155	146	001	395920	        1/8/2016 0:00	391860	        12/14/2015 0:00	 4060	         4060	                35.81	2/1/2016 0:00
144	156	146	001	395920	        1/8/2016 0:00	391860	        12/14/2015 0:00	 4060	         4060	                1.6	2/1/2016 0:00
145	157	146	001	395920	        1/8/2016 0:00	391860	        12/14/2015 0:00	 4060	         4060	                3.22	2/1/2016 0:00
142	154	146	002	529060	        1/8/2016 0:00	525560	        12/14/2015 0:00	 3500	         3500	                20.93	2/1/2016 0:00
143	155	146	002	529060	        1/8/2016 0:00	525560	        12/14/2015 0:00	 3500	         3500	                30.87	2/1/2016 0:00
144	156	146	002	529060	        1/8/2016 0:00	525560	        12/14/2015 0:00	 3500	         3500	                1.38	2/1/2016 0:00
145	157	146	002	529060	        1/8/2016 0:00	525560	        12/14/2015 0:00	 3500	         3500	                2.78	2/1/2016 0:00
142	154	146	003	1193330	        1/8/2016 0:00	1192410	        12/14/2015 0:00	 920	         920	                5.5	2/1/2016 0:00
143	155	146	003	1193330	        1/8/2016 0:00	1192410	        12/14/2015 0:00	 920	         920	                8.11	2/1/2016 0:00
144	156	146	003	1193330	        1/8/2016 0:00	1192410	        12/14/2015 0:00	 920	         920	                0.36	2/1/2016 0:00
145	157	146	003	1193330	        1/8/2016 0:00	1192410	        12/14/2015 0:00	 920	         920	                0.73	2/1/2016 0:00
142	154	146	004	1115440	        1/8/2016 0:00	1112180	        12/14/2015 0:00	 3260	         3260	                19.49	2/1/2016 0:00
143	155	146     004	1115440	        1/8/2016 0:00	1112180	        12/14/2015 0:00	 3260	         3260	                28.75	2/1/2016 0:00

utilitymeters

meterid	   name	                shortname	graduated	mincharge  flatcharge
147	   Ames Water	         AMES W	        1	        0             22.5
28	   Belton Water & Sewer	 BEL-WS	        1	        0	      25
150	   Benton Sewer	         BENT S	        1	        0             15
151	   Benton Surcharge	 BENTON	        0	        0	      13
149	   Benton Water	         BENT W	        1	        0	      11.3
277	   Berthoud Sewer	 BERTS	        0	        0	      0
276	   Berthoud Water	 BERTW	        0	        0	      11.5
200	   Bloomington GW Sewer	 BLMGWS	        0	        0	      7.9
199	   Bloomington GW Water	 BLMGWW    	1	        0	      9.83
105	   Canon City Sewer	 CANONS	        0	        0	      20.92

I would like a new table that contains [utilid], [meterid], [propid], [postdate], [amount] from the first table (utilityhistory) and [flatcharge], [mincharge] from the second table (utilitymeters).

I think what I need to do is create a new table selecting the columns from utilityhistory that i want and then add two columns to my new table, create a relationship to utilitymeters using [meterid] and populate [flatcharge], [mincharge] using a DAX formula like: flatcharge =  related('ruhor_views utilitymeters'[flatcharge]). I do not think I can do what I want in one step using a JOIN or UNION.

Any recommendations?

Thanks!

[Twan]

You can create a table that you want using DAX and the related function like you mentioned. It will perform better though if you create the table using the query editor or in the source database.  

If you have a sql connection to both of the tables the easiest way would be to select just the columns you want from each table and then inner join the two tables.  

If you don't have a sql connection then you can use the Query Editor's merge queries options.  They give examples of merging data in this article (you have to scroll down a bit more for the part on merging).  It is also pretty much the same thing as merging queries in Power Query for Excel.

Here is the basic flow you should follow to create a new table that contains columns from both tables:

1. Add another utilitymeters table (only do this if you want to keep the original utilitymeters table).
2. In the Query Editor select this new table and then Merge Queries
   1. Select utilityhistory as the table to merge
   2. Select meterid in both tables
   3. Set Join Kind as Left Outer
   4. 
3. Now expand the new columns and only keep the columns you want from the utilityhistory table
   1. 
4. Remove any extra columns you don't need from the utilitymeters table

[sboulanger]

Hi Twan,

What's the difference in level of performance in doing it in the query editor instead of using Dax? Is it really more performant?

[ruhor]

I will do this using the Query Editor instead of DAX, I'll let you know how it goes. Thanks!

[sboulanger]

Hi,

Can you explain why exactly you want to put them in one table? I'll suggest to create a reliationship between both table and then you'll be able to use both data in any dashboards.

You can also create the relationship and then add the column from one of the two table depending on which direction you've created the relationship with the fonction RELATED. 

If you want to absolutely create a new table, I'll suggest to create a new connexion on the table containing all the meterid value and only importing the meter id in order to create the relationship between the tables. THen use the RELATED function to get the column needed. Please keep in mind that it need to be 1 to 1 relationship in order to do this. In a case of an 1 to N relationship it's always the N side that'll have the RELATED function available.

Regards 

Samuel Boulanger

[ruhor]

The most honest answer of why I want to do this would probably be, because I want to improve my Power BI skills.  The reason I started going down this path is because I need to use a line chart to display the billing breakout of all the single components that go into a residential water bill. The components of each bill depend on state and local regulations and differs depending on the region. In a power BI line chart I can only add one data source (from what I have seen), but if I have a single column called [amount] and the sum of amount is composed of multiple sources (service fee, surcharge, metered water,...) then Power BI will plot them on different lines. (see example image).