Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Need help with a table join and or calculation

I have 2 tables

TableA

MonthLocationDowntime
JanuaryLA1566
JanuaryHTX2213
JanuarySTL766
FebruaryLA1914
FebruaryHTX2901
FebruarySTL1309
MarchLA3373
MarchHTX1727
MarchSTL1849

 

TableB

MonthLocationScheduledTime
JanuaryLA2139
JanuaryHTX3649
JanuarySTL3343
FebruaryLA2288
FebruaryHTX3132
FebruarySTL2622
MarchLA2861
MarchHTX3447
MarchSTL3163

 

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

 JanuaryFebruaryMarch
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? 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.