cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

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

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

2 REPLIES 2
Microsoft v-jiascu-msft
Microsoft

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

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

Microsoft v-jiascu-msft
Microsoft

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

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,515)