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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors