Replace values that are not matching in the relationship with the first value of the second table

[bunyamin360]

Hi all,

I have two tables, 'table 1' and 'table 2' and they have a many-to-one relationship through 'Column C'.

I want to replace values from 'table 1' that are not matching with the first value of another table.

I have two tables that looks like this:

Table 1

Column A	Column B	Column C
One	A	Test 1
Two	B	Test 2
Three	C	Test 3
Four	D	Test 4
Five	E	Test 5

Table 2

ID	Column C
1	Test 2
2	Test 4
3	Test 5

Can I use a Power Query that can ensure that this happens with table 1? :

Column A	Column B	Column C
One	A	Test 2
Two	B	Test 2
Three	C	Test 2
Four	D	Test 4
Five	E	Test 5

Can this be done? Thansk in advance!

[Anonymous]

It can be done in so many ways. This is one of the possible ways. PS If you encounter any problems in adapting the script to the real case and want to be helped quickly, please provide a more generous explanation.

[edhans]

@bunyamin360 you should do this with a merge if possible. It will be more steps, but will perform much faster over a larger dataset. However, I cannot figure how how table 1 relates to table 2. Why in Table 1 do you want to change the One/A row to Test 2 in Column C? You haven't explained the relationship, not at least that I can see.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[bunyamin360]

Hi @edhans

There is a one to many relationship between table 2 and table 1 on both 'column C'. The report at the moment does not show certain values because the key (column c) of table 1 does not match with the foreign key (column c) of table 2. I want the values that do not match to be the first value of table 2. In this example it would be the value 'Test 2' of table 2.

[edhans · Accepted solution]

Ok @bunyamin360 - Note that other solutions may be more elegant and require fewer steps but may not peform as well over larger datasets. A few hundred or even low thousand records it doesn't matter. Over that though and a Merge operation will generally give you better performance in a refresh. The reason is a merge does the entire table at once whereas other solutions that might look at one row at a time then compare to a value in Table 2 does it each row at a time, and will slow down over larger data sets.

I got this result:

Using this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9LVdJRcgTikNTiEgVDpVidaKWQ8nyggBNM0AgimFGUClLrDBM2Bgu75ZcWAUVcYKImENHMMpBaV5ioqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
    DefaultValue = List.First(#"Table 2"[Column C]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Column C"}, #"Table 2", {"Column C"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Column C"}, {"Column C.1"}),
    #"Added New Column C" = Table.AddColumn(#"Expanded Table 2", "New Column C", each if [Column C.1] = null then DefaultValue else [Column C.1], Text.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New Column C",{"Column A", "Column B", "New Column C"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"New Column C", "Column C"}})
in
    #"Renamed Columns"

It assigns the first value in Table 2 to a DefaultValue variable. Then it does a merge. If there is no match no match on the merge it replaces it with the variable.

[bunyamin360]

Thank you @edhans . I used this method because I already did something similar like this to generate key's for both tables. I noticed that the matching did not work at first because the joins in query editor are case sensitive while this was not occuring when setting up relations.

This made me found the source of the issue and serves as the best solution for me too.

Thank you for the support!

[edhans]

Glad to help @bunyamin360 

By the way, a trick in merging, if you will use a Fuzzy Merge, then set the threshold to 1, it will match in a case insensitive manner, but use no other fuzzy logic, so Apple will match apple or APPLE, but not APLE

[AlexisOlson]

You can add a column transformation step like this:

Table.TransformColumns(#"Previous Step",
    {{"Column C", each
      if List.Contains(Table2[Column C], _)
      then _
      else List.First(Table2[Column C]),
      type text}}
)

[bunyamin360]

Thanks! I am close to the solution now thanks to you. It does not recognize the 'then' statement though so I currently have this:

Column A	Column B	Column C
One	A	Test 2
Two	B	Test 2
Three	C	Test 2
Four	D	Test 2
Five	E	Test 2

What could the issue be?

[AlexisOlson]

I can't tell without seeing what you wrote. Probably a syntax error.

[bunyamin360]

I copy/pasted your code and changed the column names to what it truly is. I double checked it for typos but did not see anything.