none
Conditional Merging / Join RRS feed

  • Question

  • Hello my problem is the next, there's a query on wich im working right now (MATRIX) and i need to merge it depending on a value of the code RS_ID if it is equal to the values 75, 72, 73 i need it to merge it with table 1 if RS_ID is different i need to merge the MATRIX table with table 2 using RS_ID and LOC_ID. also i have already a lot of steps going on, i dont know how to apply the code without deleting those steps in the advanced editor .
    Friday, November 8, 2019 5:43 PM

All replies

  • Hello

    way to little information for me.

    Rs_id is a column i suppose. Normally you can merge only lists and tables. But you are reading a row. Or is rs_id more like a parameter for a whole table?

    Jimmy


    Query it

    Saturday, November 9, 2019 12:39 PM
  • The best way would be to split up your tables into two, one that has RS_ID {75,72,73} and one that has something else. For each of those tables, you can make a join and then append the two different tables together. I believe this would give better performance than the alternative, but I haven't tested.

    The alternative is to use something similar to this code:

    let
        Source = [your table]
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"RS_ID", Int64.Type}, {"key", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "NewCol", (Current) => 
    if List.Contains({75,72,73},Current[RS_ID]) then 
    Table.SelectRows(table1, each [key] = Current[key])
    else Table.SelectRows(table2, each [key2] = Current[key2] and [key3] = Current[key3])
    
    in
        #"Added Custom"

    Monday, November 11, 2019 8:37 AM