Forum Discussion

ECE's avatar
ECE
Icon for Advocate II rankAdvocate II
7 years ago

Query m merge with if condition

Hi,

 

Can anyone tell me how to merge two queries based on value in primary query?

 

My main dataset includes a column called Sales doc and a column called Sales Doc Item. Depending on the value in the third column "Source" these should be joind with either "P02 sales Doc" and "P02 saled doc item" OR "P08 Sales Doc" and "P08 Sales doc item".

If joining only on sales doc, I am think something in terms off:

 

#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Sales Doc"},Table1, {if "Souce" = "P08" then "P08 Sales Doc" else "P02 Sales Doc"},"Table1",JoinKind.LeftOuter),

 

The editor do in fact accept the formular above, but it does not understand that "Souce" refers to the "Source" column.

 

If this gets working, I would extend the formular to something like:

 

#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Sales Doc", "Item"},Table1, {if "Souce" = "P08" then "P08 Sales Doc" else "P02 Sales Doc", if "Source" = "P08" then "P08 Item" else if "P02 item" = null then "Item" else "P02 item"},"Table1",JoinKind.LeftOuter),

 

The last if, is because in some cases I don't have an item no, so I would need to join only on "Sales Doc"

 

Any ideas

 

Need Help

4 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Icon for Memorable Member rankMemorable Member
    7 years ago

    This is a little hard to follow without an example. Is it posible to add an example, with expected outcomes?

     

    From what I understand of the question, you could try adding a custom column. 

     

    In the custom column, you could add:

     

     

    if [Source] = "P08"

    then

    Table.NestedJoin(#"Changed Type",{"Sales Doc"},Table1, "P08 Sales Doc","Table1",JoinKind.LeftOuter)

    else if

    [P02 item] = null

    Table.NestedJoin(#"Changed Type",{"Sales Doc"},Table1, "Item","Table1",JoinKind.LeftOuter)

    else

    Table.NestedJoin(#"Changed Type",{"Sales Doc"},Table1, "P02 Sales Doc","Table1",JoinKind.LeftOuter)

     

     

    • ECE's avatar
      ECE
      Icon for Advocate II rankAdvocate II
      7 years ago

      Hi Steve,

       

      First of all thank you for you replay.

       

      I need to return 4 columns from the sub query.

      As default I will be joining two columns from the main query with the sub query, However, depending on the value of "Source" in the main query, I will need to join with different columnns in the sub query. I addition to that, if "Source" = "P02" AND "P02 item" in the SUB query is null, then I will only join on one column.

       

      I have tried your solution, but with just 4 rows in both the main and sub query, I get 16 rows return after the join. Not sure if it is possible to do anyting about that. I am returning a table, and then expanding the main query from this table.

       

      OBS: The first merge was an attempt to check if "P02 item" was blank in the sub query. Maybe this could be done directly, but don't know how to reference the sub query columns directly.

       

          #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Sales Doc", "Item"},Table1,{"P02 Sales Doc", "P02 item"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Text"}, {"Text"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "asCustom", each if [Source] = "P08" then 
Table.NestedJoin(#"Expanded Table1",{"Sales Doc", "Item"},Table1, {"P08 Sales Doc", "P08 Item"},"Table1",JoinKind.LeftOuter) 
else if
[Text] = null then
Table.NestedJoin(#"Changed Type",{"Sales Doc"},Table1, {"P02 Sales Doc"},"Table1",JoinKind.LeftOuter)
else
Table.NestedJoin(#"Expanded Table1",{"Sales Doc", "Item"},Table1, {"P02 Sales Doc", "P02 item"},"Table1",JoinKind.LeftOuter)),
    
#"Expanded asCustom" = Table.ExpandTableColumn(#"Added Custom", "asCustom", {"Table1"}, {"asCustom.Table1"}),
    #"Expanded asCustom.Table1" = Table.ExpandTableColumn(#"Expanded asCustom", "asCustom.Table1", {"Text"}, {"new.Text"})

       

      As an alternative I could merge the two queries three times. Each time return the 4 columns, and then afterwards, create 4 new custom columns, based on the first 12 columns returned - that just seems very unefficient.

    • ECE's avatar
      ECE
      Icon for Advocate II rankAdvocate II
      7 years ago

      Hi Xiaoxin,

       

      I have looked at the link you shared, but I am unable to see how this will help joining with another table. In this example the values to return is hard coded within the function.

      I have a main query of 500.000+ rows, which needs to return values from a second sub query of 300.000+ rows.