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

[Anonymous]

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?

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answer as solution?

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

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.

You can find a demo in the attachment.

Best Regards,

Dale

[Anonymous]

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