Replace values in table with values from calculated table based on a condition

[Anonymous]

Dear all,

I'm dealing with store visitation data analysis.

The data is very simple - 3 columns: date, store number (8 in total), number of visitors in.

There are 2 data sources - Dataflow and DirectQuery. Due to the some "features" of data collection there is an error which results in inaccuracy in a Dataflow dataset, where only 6 stores have correct data. So 2 incorrect ones must be replaced with values from DirectQuery, and for me, as a newby, this is not a trivial task. It can't be solved with DAX or simple Data Transform, so I believe it can be done with Power Query M, but have no idea extept using Table.ReplaceValue.

My tables look as following - so lets say number of visitors for stores #3 and #8 must be replaced with values from same shape, but calculated table:

Data	Store Number	Visitors
01/01/2021	1	123123
01/01/2021	2	123123
01/01/2021	3	34234
01/01/2021	4	324435
01/01/2021	5	545634
01/01/2021	6	435345
01/01/2021	7	324234
01/01/2021	8	123123

Thanks in advance!

Regards, Taras

[RobertPaegelow]

Hi Taras1!

I don

To replace the values in the "Visitors" column for stores 3 and 8 in your dataset with values from another table, you will probably have to use "Table.ReplaceValue" function in Power Query. This function allows you to replace specific values in a table with new values based on a matching condition.

To use the "Table.ReplaceValue" function, you can follow these steps:

1. In Power BI, open the dataset that contains the table you want to modify.

2. In the Power Query editor, select the table that you want to modify.

3. In the "Transform" tab, click the "Replace Values" button. This will open the "Replace Values" dialog box.

4. In the "Replace Values" dialog box, select the column that contains the values you want to replace in the "Column" field.

5. In the "Value to Find" field, enter the values that you want to replace. In your case, these would be the values in the "Visitors" column for stores 3 and 8.

6. In the "Replacement Value" field, enter the new values that you want to use to replace the old values. In your case, these would be the values from the other table that you want to use to replace the incorrect values in the "Visitors" column.

7. Click "OK" to apply the replacement.

After you have replaced the values in the "Visitors" column, you can use the modified table in your report just like any other table in the dataset. You can also use the Power Query editor to modify or remove the replacement if necessary.

Hope this helps you in some way...

[Anonymous]

Hi Roberts,

thanks for answering, but at step 4 I got stucked - there is just no dialog window, just 2 fields like "Find - Replace with", that's it. Maybe I do something wrong?

And re step 6 - are you sure that it will work with calculated table? It's not even shown in Query Editor mode in the list of tables.

[RobertPaegelow]

Hi, i think you have to format the column as text first and then to number again.

[Anonymous]

Unfortunatelly it doesn't make sence, you still have the same interface with no selecting options (