Null values in Append Table

[Anonymous]

I have 2 tables from an API call with different endpoints (ODBC), The tables have identical columns as they are from the same API source. There is only an identifier change in the URL, the query parameters are identical. I need to create a calculation based on one column [Ex] from Table 1 and subtracting it from [E] from Table 2. I have tried creating a measure instead of appending the tables but it will only give me the sum of each column - the sum of the other. 

If I append the two tables, it only gives me values in one half of he apended table, the 2nd half all values show as null. I have been stuck with this for 3 days now, any help would be very much appreciated.

Thanks

James

[Anonymous]

Does this code accomplish what you are describing?

let
Source = Table.FromColumns(Table.ToColumns(Table1) & Table.ToColumns(Table2),{"T1","T2"}),
#"Added Custom" = Table.AddColumn(Source, "Total", each List.Sum({[T1], [T2]}))
in
#"Added Custom"

[Anonymous]

@Anonymous I have tried putting the formula in but it seemed to return an error, this may just be my inputting it wrong, I have pasted the images of the tables below to show what my issue is and hopefully it should be clearer as my last description was a bit convoluted. Any advice would be much appreciated.

I am looking to calculate Column 3 [ExSPV] in table 2 - Column 2 [E] , I have tried to append the table but when I do the table I try to append to the current table always comes out with 'nul'l for each row as in the image below.

I have tried a union, I have tried to merge the tables but it got confusing and I got in a mess. I created a new measure which worked, but gave me a sum of the two columns rather than a complete column with all of the rows visible.

The issue is when I connect my data source to an excel file and perform the calculations in there and then connect to PowerBi as a static connection, it works, but this would mean manually updating the excel file every 15 minutes in order to have the PowerBi data available on a push refresh setup.

Any help would be so much appreciated.

James