CODEWITHSUNDEEP
excelreplacefindpowerquery
Sav
Sav

Reputation: 25

Power Query Custom Column Find and Replace

I'm using Excel Power Query, and I'm trying to create a column in my data that essentially mass finds/replaces a column in one table using columns from another table.

For context - I have table that is sales info by employeeID, by day (called Sales.) An employee can only have 1 active id assigned to them. However, there are instances where an employee's id may change. As of right now, when that occurs, none of their sales from their old ID count towards that employee (until we go in an do a manual vlookup, but it's extremely time consuming and has to be done daily when this happens)

I have a mass roster table, that shows every employees current id, along with their historic id (if they have one.) What I'm trying to do is say, if the employee id in the sales data = the employees historic id in the roster, then replace it with the current id.

I tried adding a custom column in the sales table that says:

 =IF [sales clerk] = #Roster[HistoricID2] THEN #Roster[CurrentID] ELSE [sales clerk]

but it's just returning sales clerk 100% of the time. If anyone has any experience with this, any help is appreciated!

Upvotes: 0

Views: 150

Answers (1)

horseyride
horseyride

Reputation: 21318

What you want to do is start with the sales info table, and home...merge queries .. to merge in the mass roster table using a left outer join, linking on the ID like in image below by clicking them

enter image description here

then use the arrows atop the merged column to expand [x] the all the fields except the old ID

If your current IDs are not in the mass roster table then you'd get some nulls for the result. If so, add column .. custom column ...

= if [new]=null then [id] else [new]

and then right click remove the helper column

enter image description here

sample code below that assumes Salesino had column id and MassRosterTable has columns old and new as per image

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source,{"id"},MassRosterTable,{"old"},"MassRosterTable",JoinKind.LeftOuter),
#"Expanded MassRosterTable" = Table.ExpandTableColumn(#"Merged Queries", "MassRosterTable", {"new"}, {"new"}),
#"Added Custom" = Table.AddColumn(#"Expanded MassRosterTable", "FinalID", each if [new]=null then [id] else [new]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"new"})
in #"Removed Columns"

Upvotes: 1

Related Questions