Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help with a table join and or calculation

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 ...
  • Vijay_A_Verma's avatar
    4 years ago

    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"