Replace values in bulk

[Anonymous]

Dear All 
@ImkeF  - if you can please look into this query 🙂  
there is a Administrative Code Master (Lookup table) which is loaded into Main Master Data table in Power Query.  Administrative Codes are subject to frequent change following organisational changes.

Cost Code	Discipline	Sub Discipline	Location	Function
247GAA0010	FIXED DEPT	COMMERCIAL	NEW CASTLE	IT
247NON0010	TW DEPT	TECHNICAL	NEW CASTLE	IT
268GAA0010	FIXED DEPT	TECHNICAL	NEW CASTLE	IT
268GAA0030	TW DEPT	NON OP	NEW CASTLE	IT
268NON0030	TW DEPT	SUPPORT FUNCTION	NEW CASTLE	IT
CORGAA0910	FIXED DEPT	SUPPORT FUNCTION	LONDON	IT
CORGAA0920	FIXED DEPT	SUPPORT FUNCTION	NEW CASTLE	IT
CORGAA6010	FIXED DEPT	SUPPORT FUNCTION	NEW CASTLE	IT
CORGAA6020	HEADCOUNT DEPT	SUPPORT FUNCTION	NEW CASTLE	IT
CORGAA6030	FIXED DEPT	SUPPORT FUNCTION	NEW CASTLE	IT

 However, for my reporting purposes I have to have the above codes as follows: 

Cost Code	Discipline	Sub Discipline	Location	Function
247GAA0010	INFORMATION SYSTEMS	NOR RECHARGE	NEW CASTLE	IT
247NON0010	INFORMATION SYSTEMS	NOR RECHARGE	NEW CASTLE	IT
268GAA0010	INFORMATION SYSTEMS	XCP RECHARGE	NEW CASTLE	IT
268GAA0030	INFORMATION SYSTEMS	XCP RECHARGE	NEW CASTLE	IT
268NON0030	INFORMATION SYSTEMS	XCP RECHARGE	NEW CASTLE	IT
CORGAA0910	INFORMATION SYSTEMS	IS BAU	LONDON	IT
CORGAA0920	INFORMATION SYSTEMS	IS BAU	NEW CASTLE	IT
CORGAA6010	INFORMATION SYSTEMS	IS BAU	NEW CASTLE	IT
CORGAA6020	INFORMATION SYSTEMS	IS BAU	NEW CASTLE	IT
CORGAA6030	INFORMATION SYSTEMS	IS BAU	NEW CASTLE	IT

So ideally I have to find a way to delete these 10 codes in the Main Master Table and then append my table (which is relatively stable). I cannot create two certain lists as the Main Master Table is subject to changes with only key cost codes being permanent

Ideas?

Thank you lots Mira 

[PhilipTreacy]

Hi @Anonymous 

Not sure I'm understanding the problem.  If your Main Master table is loaded from somewhere into PQ, then when the Admin Codes in that table change, you just need to refresh the query in PQ to load/update the table?  This effectively 'delete's the codes in your Main Master table and loads the new ones.

regards

Phil

[Anonymous]

Hi Phil
I am now testing @smpa01  suggestion I think it will work. Admin Codes come inside Main Master Table and I need Master Table as is but I must overwrite Admin Codes related to IT with my own mapping. So @smpa01 picked up my problem correctly.
I knew that it is a "list of list" issue that will resolve it. 
Thank you for your attention 🙂 Much appreciated 

[smpa01 · Accepted solution]

@Anonymous  what you can do here is

let's suppose this is you new value table

//tablename newval

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/Replace-values-in-bulk/m-p/1629873#M49960"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(5) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(5) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(5) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(5) > * > TR > :nth-child(5)"}}, [RowSelector="TABLE:nth-child(5) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cost Code", type text}, {"Discipline", type text}, {"Sub Discipline", type text}, {"Location", type text}, {"Function", type text}})
in
    #"Changed Type1"

 once you append the new val to masterdata and follow the steps. In this way you can always ensure that the up-to-date values are reflected for each code

//tbl masterdata

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/Replace-values-in-bulk/m-p/1629873#M49960"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(2) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(2) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(2) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(2) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(2) > * > TR > :nth-child(5)"}}, [RowSelector="TABLE:nth-child(2) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Appended Query" = Table.Combine({#"Promoted Headers", newval}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"Cost Code"}, {{"ad", each _, type table [Cost Code=nullable text, Discipline=nullable text, Sub Discipline=nullable text, Location=nullable text, Function=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad], y = Table.AddIndexColumn(x, "Index", 1, 1, Int64.Type), z = Table.SelectRows(y, each [Index] = List.Max(y[Index])) in z),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Cost Code", "Discipline", "Sub Discipline", "Location", "Function", "Index"}, {"Cost Code", "Discipline", "Sub Discipline", "Location", "Function", "Index"})
in
    #"Expanded Custom"

[Anonymous]

Thank you very much. An elegant solution. Now I am trying to replace values (change words) as per Imke's post
Thank you all!

[Anonymous]

I know that I can merge two tables, exclude those matching to my table, delete the rows in the Main Master Table and then append my own code table. But is it the only way of doing things?