Table visual: I need left join instead of visual

[alks_skla_f]

Hello, I have 2 tables: "hst and grdf - user input" (direct query) and hst_grdf_Temp (import mode).

When I select table visual and pick columns from both table I am getting only 1 row, because only one row is common for these both tables. How can I set left join? (I want all rows from hst_grdf_Temp)

[Sandip_Palit · Accepted solution]

This process combines your two tables during the data import step, giving you exactly the result you want.

Open Power Query Editor: In Power BI Desktop, go to the Home tab and click Transform data.

Select Your "Left" Table: In the Power Query Editor, select your hst_grdf_Temp query from the list on the left. This is the table from which you want to keep all the rows.

Start the Merge: On the Home ribbon, click the Merge Queries button.

Configure the Join: In the "Merge" window, configure the join as follows:

The top table should already be hst_grdf_Temp.

In the second dropdown, select your hst and grdf - user input table.

Click on the common key column in both tables to tell Power Query how to match them.

This is the most important step: For the Join Kind, select Left Outer (all from first, matching from second).

Click OK.

Expand the New Column:

A new column will be added to your hst_grdf_Temp table. The column header will be the name of the other table (hst and grdf - user input), and the cells will contain the word "Table".

Click the Expand icon (two opposite-facing arrows) in that new column's header.

A dropdown will appear. Uncheck "Use original column name as prefix" and select only the columns you want to bring into your hst_grdf_Temp table.

Click OK.

Load the Changes: On the Home tab, click Close & Apply.

Your hst_grdf_Temp table in the Power BI model will now contain all of its original rows, with the matching data from the second table added as new columns. You can now build your table visual using only this single, merged table to get the desired result.

If this explanation and solution resolve your issue, please like and accept the solution.

[v-agajavelly]

Hi @alks_skla_f ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Thanks,
Akhil.

[v-agajavelly]

Hi @alks_skla_f ,

checking back were you able to try those steps and confirm if everything is working as expected now? If any issues are still persisting or new ones popped up, let me know and we can troubleshoot further together.

Regards,
Akhil.

[v-agajavelly]

Hi @alks_skla_f ,

Just following up to see if you got a chance to test the steps we discussed earlier. Happy to know if it solved your issue or if you ran into anything unexpected while trying it out.

Regards,
Akhil.

[v-agajavelly]

Hi @alks_skla_f 

Thanks a lot @Royel , @Sandip_Palit for sharing the detailed steps and providing both Power Query and DAX approaches that’s really helpful.

@alks_skla_f  did you get a chance to look into the solution shared by the super user? Were you able to try it out and see if it works for your case? If you run into anything or get stuck while implementing it, feel free to share  we can help you get it working smoothly.

Regards,
Akhil.

[Royel · Accepted solution]

Hi @alks_skla_f  Thank you for asking questions 

@Sandip_Palit  posted optimal way to solve this problem by using power query. Since, you conside "hst and grdf - user input" as direct query, seems like you have large data. If you do the transformation in the power query it will break the query folding. 

Here is another way to overcome the same functionalities: 

We need to create a new table based on selected columns between two tables

Dax: Create Table

New Tables = 
VAR Table1Modified = SELECTCOLUMNS(
    'hst and grdf - user input', 
    "Name", 'hst and grdf - user input'[Name] & "",
    "Val1", 'hst and grdf - user input'[Val]
)
VAR Table2Modified = SELECTCOLUMNS(
    hst_grdf_Temp, 
    "Name", hst_grdf_Temp[Name] & "",
    "Val2", hst_grdf_Temp[Val]
)
RETURN NATURALLEFTOUTERJOIN(Table1Modified, Table2Modified)

Now, visualize new tables data and see the results: 

As you can see we have all records from left table and keep only matched records data Val2 from right table. 

Note: I Keep the model relationshp same as you have. You just need to update column names.

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[Sandip_Palit · Accepted solution]

This process combines your two tables during the data import step, giving you exactly the result you want.

Open Power Query Editor: In Power BI Desktop, go to the Home tab and click Transform data.

Select Your "Left" Table: In the Power Query Editor, select your hst_grdf_Temp query from the list on the left. This is the table from which you want to keep all the rows.

Start the Merge: On the Home ribbon, click the Merge Queries button.

Configure the Join: In the "Merge" window, configure the join as follows:

The top table should already be hst_grdf_Temp.

In the second dropdown, select your hst and grdf - user input table.

Click on the common key column in both tables to tell Power Query how to match them.

This is the most important step: For the Join Kind, select Left Outer (all from first, matching from second).

Click OK.

Expand the New Column:

A new column will be added to your hst_grdf_Temp table. The column header will be the name of the other table (hst and grdf - user input), and the cells will contain the word "Table".

Click the Expand icon (two opposite-facing arrows) in that new column's header.

A dropdown will appear. Uncheck "Use original column name as prefix" and select only the columns you want to bring into your hst_grdf_Temp table.

Click OK.

Load the Changes: On the Home tab, click Close & Apply.

Your hst_grdf_Temp table in the Power BI model will now contain all of its original rows, with the matching data from the second table added as new columns. You can now build your table visual using only this single, merged table to get the desired result.

If this explanation and solution resolve your issue, please like and accept the solution.