replace values based on column from different query

[Anonymous]

Hi there, Sorry this seems to be pretty easy but for some reason it doesn't work for me. I have query 1 with 2 columns which are region name and region code, and I have second query only with region code column. I'm trying to replace the region codes in second query with corresponding region names from the first query. The following is my code. It doesn't through any errors but it does nothing, so I'm not sure what am i doing wrong. Thanks! Custom1 = Table.ReplaceValue(#"Removed Other Columns",each TestQuery1[Region],each TestQuery1[Region Name],Replacer.ReplaceValue,{"Region"})

[camargos88]

Hi @Anonymous ,

Have you tried to merge them ?

Be aware with start/end spaces and upper/lower.

[Anonymous]

Thanks @camargos88 for your response. Merging does work but I have pretty big tables and further actions makes it pretty time consuming and affects the performance badly, so I would prefer to not use merge function at this stage.

[camargos88]

@Anonymous ,

You cannot replace values from one table to another without a merge/lookup (using table/list).

Another option is creating a relationship between them.

[Anonymous]

@camargos88 can I create relationship in power query or do i have to use power BI desktop for that?

[Anonymous]

@camargos88 is it possible to kind of link the 2 queries as below? Once again it doesn't though any errors but it doesn't work

let
Source = Web.Page(Web.Contents("SOME URL")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Region Name", type text}, {"Region", type text}, {"Endpoint", type text}, {"Protocol", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Region"}),
Source1 = TestQuery1,
#"Region Name" = Source1[Region Name],
#"Converted to Table" = Table.FromList(#"Region Name", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Region" = Source1[Region Name],
#"Converted to Table1" = Table.FromList(#"Region", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Custom1 = Table.ReplaceValue(#"Removed Other Columns",each #"Region",each #"Region Name",Replacer.ReplaceValue,{"Region"})
in
Custom1

[camargos88]

@Anonymous ,

create it on the model view, out of power query.