Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

5 REPLIES 5
PhilipTreacy
Super User
Super User

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



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!


Anonymous
Not applicable

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

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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
Not applicable

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors