Need help with a table join and or calculation

[dpibarker]

I have 2 tables

TableA

Month	Location	Downtime
January	LA	1566
January	HTX	2213
January	STL	766
February	LA	1914
February	HTX	2901
February	STL	1309
March	LA	3373
March	HTX	1727
March	STL	1849

TableB

Month	Location	ScheduledTime
January	LA	2139
January	HTX	3649
January	STL	3343
February	LA	2288
February	HTX	3132
February	STL	2622
March	LA	2861
March	HTX	3447
March	STL	3163

each table has other rows (in the real data) and I cannot merge them within each table.  

What I need is a measured calculation that takes 

(Downtime / ScheduledTime) / 60

So then if I built a talble to show these results, it would show

	January	February	March
LA	.0122	.0139	.0196
HTX	.0101	.0154	.0084
STL	.0038	.0083	.0097

When I type in this calculation, I am getting totally wrong numbers. 

Instead I get:

	January
LA	.7787
HTX	.4564
STL	.4983

What am I doing wrong? 

[Vijay_A_Verma · Accepted solution]

This can be done in PQ very easily.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

TableA

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUfJxBBKGpmZmSrE6yOIeIRFA0sjI0BhNIjjEB0iaQzW4pSYVoZhkaWiCLgE1ytLAEF0GYpahsYElWMY3sSg5A2aSsbG5MYooxBhDcyNzFGGoGRYmQDNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Location = _t, Downtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Location", type text}, {"Downtime", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Month", "Location"}, TableB, {"Month", "Location"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"ScheduledTime"}, {"ScheduledTime"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "Value", each [Downtime]/[ScheduledTime]/60),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Downtime", "ScheduledTime"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month]), "Month", "Value", List.Sum)
in
    #"Pivoted Column"

 In case, you need - Code for TableB

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUfJxBBJGhsaWSrE6yOIeIRFA0tjMBF0iOMQHJGFsYgyWcEtNKkIxysjCAl0CapahsRG6DMQwIzMjiIxvYlFyBtwkCzNDFFGoMSYm5ijCUAcZmgEdFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Location = _t, ScheduledTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Location", type text}, {"ScheduledTime", Int64.Type}})
in
    #"Changed Type"