SQL join Statement to query a Power BI Dataflow

[PBIDEV_10]

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

[v-xinruzhu-msft]

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.

[PBIDEV_10]

Hello @v-xinruzhu-msft 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?

[lbendlin]

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).

[lbendlin]

Do you want to do the join in Power Query or at the data source?

[PBIDEV_10]

Hello! I want to have this join in power query.