cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mira_abel
Helper I
Helper I

Replace values in bulk

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 CodeDisciplineSub DisciplineLocationFunction
247GAA0010FIXED DEPTCOMMERCIALNEW CASTLEIT
247NON0010TW DEPTTECHNICALNEW CASTLEIT
268GAA0010FIXED DEPTTECHNICALNEW CASTLEIT
268GAA0030TW DEPTNON OPNEW CASTLEIT
268NON0030TW DEPTSUPPORT FUNCTIONNEW CASTLEIT
CORGAA0910FIXED DEPTSUPPORT FUNCTIONLONDONIT
CORGAA0920FIXED DEPTSUPPORT FUNCTIONNEW CASTLEIT
CORGAA6010FIXED DEPTSUPPORT FUNCTIONNEW CASTLEIT
CORGAA6020HEADCOUNT DEPTSUPPORT FUNCTIONNEW CASTLEIT
CORGAA6030FIXED DEPTSUPPORT FUNCTIONNEW CASTLEIT

 

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

Cost CodeDisciplineSub DisciplineLocationFunction
247GAA0010INFORMATION SYSTEMSNOR RECHARGENEW CASTLEIT
247NON0010INFORMATION SYSTEMSNOR RECHARGENEW CASTLEIT
268GAA0010INFORMATION SYSTEMSXCP RECHARGENEW CASTLEIT
268GAA0030INFORMATION SYSTEMSXCP RECHARGENEW CASTLEIT
268NON0030INFORMATION SYSTEMSXCP RECHARGENEW CASTLEIT
CORGAA0910INFORMATION SYSTEMSIS BAULONDONIT
CORGAA0920INFORMATION SYSTEMSIS BAUNEW CASTLEIT
CORGAA6010INFORMATION SYSTEMSIS BAUNEW CASTLEIT
CORGAA6020INFORMATION SYSTEMSIS BAUNEW CASTLEIT
CORGAA6030INFORMATION SYSTEMSIS BAUNEW CASTLEIT

 

 

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 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@mira_abel  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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

5 REPLIES 5
PhilipTreacy
Super User
Super User

Hi @mira_abel 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
Super User
Super User

@mira_abel  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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

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

mira_abel
Helper I
Helper I

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?

 

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors