Need help with a table join and or calculation

Question

I have 2 tablesTableAMonthLocationDowntimeJanuaryLA1566JanuaryHTX2213JanuarySTL766FebruaryLA1914FebruaryHTX2901FebruarySTL1309MarchLA3373MarchHTX1727MarchSTL1849&nbsp;TableBMonthLocationScheduledTimeJanuaryLA2139JanuaryHTX3649JanuarySTL3343FebruaryLA2288FebruaryHTX3132FebruarySTL2622MarchLA2861MarchHTX3447MarchSTL3163&nbsp;each table has other rows (in the real data) and I cannot merge them within each table.&nbsp;&nbsp;What I need is a measured calculation that takes&nbsp;&nbsp;(Downtime / ScheduledTime) / 60&nbsp;So then if I built a talble to show these results, it would show&nbsp;JanuaryFebruaryMarchLA.0122.0139.0196HTX.0101.0154.0084STL.0038.0083.0097&nbsp;When I type in this calculation, I am getting totally wrong numbers.&nbsp;Instead I get:&nbsp;JanuaryLA.7787HTX.4564STL.4983What am I doing wrong?&nbsp;

vijay_a_verma · Accepted Answer

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 testTableAlet
    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"&nbsp;In case, you need - Code for TableBlet
    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"

Forum Discussion

Need help with a table join and or calculation

1 Reply

Related content

Direct Lake Model - Calculated Column/Tables

How to search specific word in table filter Calculations

Table Visual not Matching Calculated Visual

Directlake semantic model, calculated table

PowerBI - Matrix table, hide values used only for a visual calculation

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

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

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

	January
LA	.7787
HTX	.4564
STL	.4983