Replace text with lookup table

[rocky7st]

I have a Power BI query that pulls data from daily spreadsheet reports as per code below.

I need advise on how I would go about replacing addresses that have been mispelt i.e.

John Doe St

John Doe Street

Jon Doe St

From what I can see the easiest way would having a lookup table and any address in that table will be replaced with the correct one. However I am new to PowerBI and Queries therefore need advise on how I would achieve this.

let
    Source = Folder.Files("C:\Users\ST\Documents\Reports\2018"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from 2018", each #"Transform File from 2018"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from 2018"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from 2018", Table.ColumnNames(#"Transform File from 2018"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Account ", type text}, {"Ready Date ", type date}, {"Docket ", type text}, {"Job# ", Int64.Type}, {"Number of Items ", Int64.Type}, {"Declared Weight ", type number}, {"Charged Weight ", type number}, {"Driver# ", Int64.Type}, {"From Name ", type text}, {"From Address ", type text}, {"From Suburb ", type text}, {"From State ", type text}, {"From Postcode ", Int64.Type}, {"Stop# ", Int64.Type}, {"To Name ", type text}, {"To Address ", type text}, {"To Suburb ", type text}, {"To State ", type text}, {"To Postcode ", Int64.Type}, {"Date of Collection ", type date}, {"Time Of Collection ", type time}, {"Date Of Delivery ", type date}, {"Time Of Delivery ", type time}, {"Expected Transit Time ", type datetime}, {"References ", type text}, {"Job Type ", type text}, {"Service Type ", type text}, {"Service Description ", type text}, {"Price$ GST Exclusive ", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"To Name ", Text.Trim, type text}, {"To Address ", Text.Trim, type text}, {"To Suburb ", Text.Trim, type text}, {"From Address ", Text.Trim, type text}, {"From Suburb ", Text.Trim, type text}}),
    #"RDS to RD" = Table.ReplaceValue(#"Trimmed Text"," RDS"," ROAD",Replacer.ReplaceText,{"To Address "}),
    #"ROAD to RD" = Table.ReplaceValue(#"RDS to RD"," ROAD"," RD",Replacer.ReplaceText,{"To Address "}),
    #"ABERNATHY to ABERNETHY" = Table.ReplaceValue(#"ROAD to RD","ABERNATHY","ABERNETHY",Replacer.ReplaceText,{"To Address "}),
    #"Removed Duplicates" = Table.Distinct(#"ABERNATHY to ABERNETHY", {"To Address ", "Date Of Delivery ", "Time Of Collection "})
in
    #"Removed Duplicates"

[v-piga-msft]

Hi @rocky7st,

You may try Power Query M function Text.Replace and Text.ReplaceRange, and DAX Function REPLACE Function and SUBSTITUTE Function.

If you want to use M query, you could have a reference of this video.

If you want to LOOKUPVALUE function, you could have a view of this blog.

If you still need help, could you share your data sample and your desired output, so that we can help further investigate on it?

Best Regars,

Cherry

[rocky7st]

Hi There,

Essentially this is what my data looks like. However I am actually dealing with 10,000 rows worth of Addresses

Address	Correct Address
1 John St	1 John St
1 John Steet	10 Proctor Lane
1 Jon Street	7 Beta Rd
Microsoft (1 John St)	2 John St
10 Proctor Lane	1 Microsoft Way
10 Procter Lane	11 Procter Lane
10 Proctor Ln	8 Beta Rd
7 Beta Rd	3 John St
7 Betta Road
7 Beta Rd
2 John St
2 Jonn Steet
2 Jon Street
1 Microsoft Way
Microsoft HQ (1 Microsoft Way)
Head Office Microsoft (1 Microsoft Way)
Microsoft (1 Microsoft Way)
11 Proctr Lane
11 Procter Lane
11 Proctor Ln
8 Beta Rd
8 Betta Road
8 Beta Rd
3 John St
3 John Steet
3 Jon Street

[v-piga-msft]

Hi @rocky7st,

For your scenario, I'm afraid that the fuctions I mentioned in first reply may not solve your problem.

You could have a try with Multi Condition Logic In Power Query In this blog, there is a sample which could help you understand how to use this function in your scenario.

Best Regards,

Cherry

[v-piga-msft]

Hi @rocky7st,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best  Regards,

Cherry