Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

how to replace some values in one table with values from another table

I have a source table that I need to "clean up". The clean-up operation must be done entirely in Power BI. In particular, I need to correct some values in a column. The correct values are stored in another source table. For simplicity, the table to clean up is like:

Table A

ID    SomeCol

1     a

2     b

3     d

4     e

5     g

 

The corrected data source is like:

Table B

ID    AnotherCol

3     c

4     d

5     e

 

The desired transformation of table A would look like:

ID    SomeCol

1     a

2     b

3     c

4     d

5     e

 

I am far more experienced in Qlik Sense than I am with Power BI. So, I'm having difficulty "un-learning" Qlik Sense to "think" in terms of Power BI. In Qlik Sense, to clean up table A, I would do something like:

-load table A

-load table B as a left join (on ID) to table A, preserving SomeCol and AnotherCol ("enhance" table A with data from table B)

-create derived (calculated) table C, which is simply a copy of table A, but with a "clean-up" statement for column SomeCol, e.g.,

If(

IsNull(AnotherCol) = -1 //no "corrected" data exists for the given row, i.e., it's already correct

,SomeCol

,AnotherCol

) as SomeCol

-drop table A (use table C moving forward)

 

I don't know if the "Qlik" solution method would work in Power BI. How can I achieve the desired result in Power BI?

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

You can apply the same solution in the Power BI Desktop. 

1. Open the Query Editor;

2. Merge the two queries as one;

3. Add a custom column;

4. Delete the unwanted columns.how to replace some values in one table with values from another table.png

 

 

 

 

 

 

 

 

 

 

 

 

 

You can find a demo in the attachment.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

Could you please mark the proper answer as solution?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

You can apply the same solution in the Power BI Desktop. 

1. Open the Query Editor;

2. Merge the two queries as one;

3. Add a custom column;

4. Delete the unwanted columns.how to replace some values in one table with values from another table.png

 

 

 

 

 

 

 

 

 

 

 

 

 

You can find a demo in the attachment.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Dale!

 

I have the same problem, but with a big BUT. Second table with correct values is DirectQuery, so I can't just manually merge them and kill some bad values. I have a table with data of a stores visitation for 8 stores< but 2 of them are inaccurate and should be replaced with data from calculated table (sourced from DirectQuery). So I think it can be somehow solved with Power Query M functions, but have no idea how.

 

Both tables are 3 columns: Date, Store Number, Visitation.

 

I'm almost lost any hope of solving it.

 

Best regards, Taras

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.