Replace multiple values with a conditional column not working?

[rmcgrath]

Hi, all - here are my steps:  used the Conditional Column wizard to create a new Site_ID column; removed the old Site_ID column; renamed the new one to "Site_ID".  I do not get any errors, but the replacements do not happen?  What am I missing?

STEP 1:

= Table.AddColumn(Source, "Custom", each if [Site_ID] = "110" then "PASCO WEST" else if [Site_ID] = "111" then "PASCO EAST" else if [Site_ID] = "331" then "LEWISTON" else if [Site_ID] = "332" then "FRANKLIN" else if [Site_ID] = "CRWTH PLANT" then "CORWITH BRKR" else if [Site_ID] = "L101" then "LAYER 1" else if [Site_ID] = "L103" then "LAYER 3" else if [Site_ID] = "L105" then "LAYER 5" else if [Site_ID] = "L106" then "LAYER 6" else if [Site_ID] = "MFF" then "MORNING FRESH" else if [Site_ID] = "SCNTR PLNT" then "SIOUX CENTER BRKR" else [Site_ID])

STEP 2:

= Table.RemoveColumns(#"Site IDs",{"Site_ID"})

STEP 3:

= Table.RenameColumns(#"Removed old Site_ID",{{"Custom", "Site_ID"}})

[MasonMA · Accepted solution]

@rmcgrath 

Hi, if in your situation, i would create a 'Site' Lookup table manually in Excel or Power BI itself(if it's a small model).

In Power Query make sure all Site_ID are of type Text. Then in Power BI Model view, create one-to-many relationship between your Lookup table and Fact table. Now your 'Site' Lookup table will be working as a dimension table. 

With your model being set up this way, the logic is reusable, scalable, and easier to maintain.

Hope it helps:)