Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @ECE,
I'd like to suggest you take a look at following link to know how to create a custom query with multiple if conditions.
Multi Condition Logic In Power Query
Regards,
Xiaoxin Sheng
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.
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)
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |