Power Query change input data

[Anonymous]

Hi everyone, I have very little experience using Power Query so this might be a simple problems for you gurus to solve. 

When importing my data to Power Bi there is several wrong entries in my data which i need to correct, as it is matched to alot of different things at a later stage. Normally i would do this with a simple calculated collumn, but as there is allready loads of calculated collumns building on this collumn it will be much easier to just edit the import. 

Currently i''m importing data from public sources meaning that i cannot change the imported excel file myself to have the correct names. Therefore, i need power query to do edits when importing. 

So what i need is help making an expression using the "advanced editor" building on if statements. 

it needs to be something like this: 
If collumn 17 = "risvik" then change collumn 3 to "....." else do nothing.

Hope you understood,

Cheers!  

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the code below.

= Table.ReplaceValue(Source, each [collumn 3], each if [collumn 17] = "risvik" then "....." else [collumn 3], Replacer.ReplaceText, {"collumn 3"})

[Anonymous]

Hi Sam, thank you for taking interest. 

Your code did not give any syntax errors. However, it did for some reason also not change the name in column 3. I've included the whole list from the advanced editor below. Could you please take a look and suggest some changes? 

What the code should do is: if column 17 = risvik then column 3 should change from "NORD NORSK STAMFISK AS" to "NORDLAKS OPPDRETT AS" if not keep the name in column 3. 

Kind regards. 

let
    Akvakulturregisteret = let
    Source = Excel.Workbook(Web.Contents("https://www.fiskeridir.no/content/download/7499/94172/version/316/file/Akvakulturregisteret.xlsx"), null, true),
    Akvakulturregisteret_Sheet = Source{[Item="Akvakulturregisteret",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Akvakulturregisteret_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"AKVAKULTURTILLATELSER PR. 31.12.2018", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"(Enhet i stk. oppgis i 1000) ", type any}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"(Enhet i stk. oppgis i 1000) _1", type any}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column13] = "Laks" or [Column13] = "Regnbueørret" or [Column13] = "Ørret")),
    #"Removed Duplicates1" = Table.Distinct(#"Filtered Rows", {"Column16"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates1", each true),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","FINNØY FISK AS","MARINE HARVEST NORWAY AS",Replacer.ReplaceText,{"Column3"}),
    #"Replaced Value2"= Table.ReplaceValue(Source, each [Column3], each if [Column17] = "RISVIK" then "NORDLAKS OPPDRETT AS" else [Column3], Replacer.ReplaceText, {"Column3"})
in
    #"Replaced Value",
    #"Filtered Rows" = Table.SelectRows(Akvakulturregisteret, each true)
in
    #"Filtered Rows"

[v-chuncz-msft]

@Anonymous,

Just choose Replace Values and change the code accordingly.