Forum Discussion

Dragonfury's avatar
Dragonfury
Frequent Visitor
3 years ago
Solved

Conditional join on a table merge

I have 2 tables

Table1

SalesPerson | Customer | Balance | SG 
Tim         | A134     |  2845   | 0
Tim         | A134     |  3847   | 1
John        | B395     |  8475   | 0
John        | C113     |  100    | 1

 

 table 2

SalesPerson | Customer | Balance | SG 
Tim         | A134     |  2845   | 0
Tim         | A134     |  3847   | 1
John        | B395     |  8475   | 0
John        | C113     |  100    | 1

(both tables are identical, as I want to actually perform a self-join, but since this is not possible in PQ I've duplicated the table to merge instead)

 

I want to merge the table2 to table1 on SalesPerson AND table2.SG=0

 

How can I achieve this in power query as it does not give me the option to do so in the merge GUI?

Need Help
Show and Tell
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    3 years ago

     

     but since this is not possible in PQ

     

    That is in fact possible.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS0OSC0qzs9TqFFwLi0uyc9NLQIynRJzEvOSU4GsYHcFpVidaKWQzFwFGKhRcDQ0NoEyFYwsTEzBLAMCCo0tTMzBLEOwQq/8jDyEQidjS1OYQqA6ZBNRFTobGhrDFBoaGCjATIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", (k)=> Table.SelectRows(#"Promoted Headers",each [#"SalesPerson "]=k[#"SalesPerson "] and [#" SG "]=" 0")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"SalesPerson ", " Customer ", " Balance ", " SG "}, {"Custom.SalesPerson ", "Custom. Customer ", "Custom. Balance ", "Custom. SG "})
in
    #"Expanded Custom"

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

     

     but since this is not possible in PQ

     

    That is in fact possible.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS0OSC0qzs9TqFFwLi0uyc9NLQIynRJzEvOSU4GsYHcFpVidaKWQzFwFGKhRcDQ0NoEyFYwsTEzBLAMCCo0tTMzBLEOwQq/8jDyEQidjS1OYQqA6ZBNRFTobGhrDFBoaGCjATIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", (k)=> Table.SelectRows(#"Promoted Headers",each [#"SalesPerson "]=k[#"SalesPerson "] and [#" SG "]=" 0")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"SalesPerson ", " Customer ", " Balance ", " SG "}, {"Custom.SalesPerson ", "Custom. Customer ", "Custom. Balance ", "Custom. SG "})
in
    #"Expanded Custom"

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".