Forum Discussion
SQL join Statement to query a Power BI Dataflow
Hello everyone!
I am creating a dataflow (Gen 2) based on the existing PBI datasett in import mode. So I started with importing power query template to the dataflow. I have used following code to connect to a single table. This is a dimension table.
Lakehouse.Contents(null){[workspaceId = "xxxxxxxxxxx"]}[Data]{[lakehouseId = "xxxxxxxxxxxx"]}[Data]
dbo_Table1 = Source{[Id="xxxxxxx",ItemKind="Table"]}[Data].
Then fact table has JOIN queryin original dataset.
SELECT a.*, b.column_name FROM Table1 a LEFT JOIN Table2 b ON b.Table1_FK = a.Table1_FK
So I tried to create somthing like this:
Source = Lakehouse.Contents(null){[workspaceId = "xxxxxxxxxxx"]}[Data]{[lakehouseId = "xxxxxxxxxxx"]}[Data],
#dbo_Table1 = Source{[Id="xxxxxxxxxxx",ItemKind="Table"]}[Data],
#dbo_Table2 = Source{[Id="xxxxxxxxxxx",ItemKind="Table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"#dbo_Table1", Condition)
But I am not getting futher than this. How to include JOIN in condition part. Also want to select one column from Table2. Anyone has idea about it?
Regards
PP
5 Replies
Do you want to do the join in Power Query or at the data source?
Hello! I want to have this join in power query.
Hi,
Thanks for the solution lbendlin offered, and i want to offer some more informaiton for user to refer to.
hello PBIDEV_10 , based on your descriotion, you can user merge queries in power query, it contains left join, you can refet to the following link about it.
Merge queries overview - Power Query | Microsoft Learn
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous Thank you for your suggestion but in this case Table2 is not used in dataset as a table but referred only into the join to filter rows in fact. Can I still use merge query option?
you need to ingest both tables separately in Power Query and then merge them (which by the way is highly not recommended - let the data model do the work for you).
