Find and replace data by date in power query

Faisal Yunianto

Faisal Yunianto

New Member
Joined
Apr 21, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello guys! I had some case about power query. This case is a little different from the case I asked about last week. If you want to see my post last week here is the link.

Replace data according to the date

In this case there are restrictions on finding and replacing data by date. In the picture, there are sample data (table BEFORE REPLACE) and table replacements. I want to find and replace data from KEVIN to TESTING from first March until the end of date and DAPO to TESTING from March 5 until the end of date.

I want the data to return to normal again after the date I specify. So, I will input the information in the table replacements. Start from March 9 from KEVIN back into KEVIN, March 13 from DAPO back into DAPO.

For everyone who can help me, I appreciate your work. Thanks!

1712481160748.png
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
The Query behind the Table Replacements table:

I named this table tbl_reps

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Find", type text}, {"Replace", type text}}),
    index = Table.AddIndexColumn(cType, "Index", 1, 1, Int64.Type),
    lists = Table.AddColumn(index, "Custom", each try {0.. Number.From(index[Date]{[Index]}) - Number.From([Date])-1} otherwise {0..300}),
    exp = Table.ExpandListColumn(lists, "Custom"),
    result = Table.AddColumn(exp, "nDates", each Date.AddDays([Date],[Custom]))
in
    result

And this behind the BEFORE REPLACE table

I named this table tbl_base

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Outlet User", type text}}),
    merge = Table.NestedJoin(cTypes, {"Date", "Outlet User"}, tbl_reps, {"nDates", "Find"}, "tbl_reps", JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(merge, "tbl_reps", {"Replace"}, {"Replace"})
in
    result

Book1
ABCDEFGHIJK
1DateOutlet UserDateFindReplaceDateOutlet UserReplace
21-3-2024KEVIN1-3-2024KEVINTESTING1-3-2024KEVINTESTING
32-3-2024KEVIN5-3-2024DAPOTESTING2-3-2024KEVINTESTING
43-3-2024KEVIN9-3-2024KEVINKEVIN3-3-2024KEVINTESTING
54-3-2024KEVIN13-3-2024DAPODAPO4-3-2024KEVINTESTING
65-3-2024DAPO5-3-2024DAPOTESTING
76-3-2024DAPO6-3-2024DAPOTESTING
87-3-2024DAPO7-3-2024DAPOTESTING
98-3-2024DAPO8-3-2024DAPOTESTING
109-3-2024KEVIN9-3-2024KEVINKEVIN
1110-3-2024KEVIN10-3-2024KEVINKEVIN
1211-3-2024KEVIN11-3-2024KEVINKEVIN
1312-3-2024KEVIN12-3-2024KEVINKEVIN
1413-3-2024DAPO13-3-2024DAPODAPO
1514-3-2024DAPO14-3-2024DAPODAPO
1615-3-2024DAPO15-3-2024DAPODAPO
Sheet1
 
Upvote 0
Solution
JEC said:
The Query behind the Table Replacements table:

I named this table tbl_reps

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Find", type text}, {"Replace", type text}}),
    index = Table.AddIndexColumn(cType, "Index", 1, 1, Int64.Type),
    lists = Table.AddColumn(index, "Custom", each try {0.. Number.From(index[Date]{[Index]}) - Number.From([Date])-1} otherwise {0..300}),
    exp = Table.ExpandListColumn(lists, "Custom"),
    result = Table.AddColumn(exp, "nDates", each Date.AddDays([Date],[Custom]))
in
    result

And this behind the BEFORE REPLACE table

I named this table tbl_base

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Outlet User", type text}}),
    merge = Table.NestedJoin(cTypes, {"Date", "Outlet User"}, tbl_reps, {"nDates", "Find"}, "tbl_reps", JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(merge, "tbl_reps", {"Replace"}, {"Replace"})
in
    result

Book1
ABCDEFGHIJK
1DateOutlet UserDateFindReplaceDateOutlet UserReplace
21-3-2024KEVIN1-3-2024KEVINTESTING1-3-2024KEVINTESTING
32-3-2024KEVIN5-3-2024DAPOTESTING2-3-2024KEVINTESTING
43-3-2024KEVIN9-3-2024KEVINKEVIN3-3-2024KEVINTESTING
54-3-2024KEVIN13-3-2024DAPODAPO4-3-2024KEVINTESTING
65-3-2024DAPO5-3-2024DAPOTESTING
76-3-2024DAPO6-3-2024DAPOTESTING
87-3-2024DAPO7-3-2024DAPOTESTING
98-3-2024DAPO8-3-2024DAPOTESTING
109-3-2024KEVIN9-3-2024KEVINKEVIN
1110-3-2024KEVIN10-3-2024KEVINKEVIN
1211-3-2024KEVIN11-3-2024KEVINKEVIN
1312-3-2024KEVIN12-3-2024KEVINKEVIN
1413-3-2024DAPO13-3-2024DAPODAPO
1514-3-2024DAPO14-3-2024DAPODAPO
1615-3-2024DAPO15-3-2024DAPODAPO
Sheet1
Click to expand...
I've tried the answer you gave but I'm having problems if the data in one column from March 1 to March 12 is KEVIN. when I want to change the data from 9 to 12 from TESTING to KEVIN. Dates 5 to 8 are empty.
1712498630612.png
 
Upvote 0
Faisal Yunianto said:
I've tried the answer you gave but I'm having problems if the data in one column from March 1 to March 12 is KEVIN. when I want to change the data from 9 to 12 from TESTING to KEVIN. Dates 5 to 8 are empty.
View attachment 109607
Click to expand...
sorry I forgot to delete the second row from the replacements table. when I deleted it, it met my expectations. it works, thank you very much bro!
1712499079836.png
 
Upvote 0
JEC said:
The Query behind the Table Replacements table:

I named this table tbl_reps

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Find", type text}, {"Replace", type text}}),
    index = Table.AddIndexColumn(cType, "Index", 1, 1, Int64.Type),
    lists = Table.AddColumn(index, "Custom", each try {0.. Number.From(index[Date]{[Index]}) - Number.From([Date])-1} otherwise {0..300}),
    exp = Table.ExpandListColumn(lists, "Custom"),
    result = Table.AddColumn(exp, "nDates", each Date.AddDays([Date],[Custom]))
in
    result

And this behind the BEFORE REPLACE table

I named this table tbl_base

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Outlet User", type text}}),
    merge = Table.NestedJoin(cTypes, {"Date", "Outlet User"}, tbl_reps, {"nDates", "Find"}, "tbl_reps", JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(merge, "tbl_reps", {"Replace"}, {"Replace"})
in
    result

Book1
ABCDEFGHIJK
1DateOutlet UserDateFindReplaceDateOutlet UserReplace
21-3-2024KEVIN1-3-2024KEVINTESTING1-3-2024KEVINTESTING
32-3-2024KEVIN5-3-2024DAPOTESTING2-3-2024KEVINTESTING
43-3-2024KEVIN9-3-2024KEVINKEVIN3-3-2024KEVINTESTING
54-3-2024KEVIN13-3-2024DAPODAPO4-3-2024KEVINTESTING
65-3-2024DAPO5-3-2024DAPOTESTING
76-3-2024DAPO6-3-2024DAPOTESTING
87-3-2024DAPO7-3-2024DAPOTESTING
98-3-2024DAPO8-3-2024DAPOTESTING
109-3-2024KEVIN9-3-2024KEVINKEVIN
1110-3-2024KEVIN10-3-2024KEVINKEVIN
1211-3-2024KEVIN11-3-2024KEVINKEVIN
1312-3-2024KEVIN12-3-2024KEVINKEVIN
1413-3-2024DAPO13-3-2024DAPODAPO
1514-3-2024DAPO14-3-2024DAPODAPO
1615-3-2024DAPO15-3-2024DAPODAPO
Sheet1
Click to expand...
sorry JEC, I unmarked your answer because i had a problem again, when I add data with the same name in the bottom row I get empty results. The problem is I have to add KEVIN's name with the date March 16 to the replacements table. The original data is very large and amounts to thousands with dozens of names, it is impossible if I have to look at the data one by one and add data to the replacements table. Can anyone help me?
1712500163320.png
 
Last edited:
Upvote 0
Everything is based on the replacement table. You have to assign the replacement values for each date period.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Faisal Yunianto
  • Solved
Replace data according to the date
Replies
5
Views
212
Faisal Yunianto
Faisal Yunianto
A
  • Question
utilisation chart for employee FTE's
Replies
0
Views
152
ajm
A
I
  • Question
Multiples operations through Power Query
Replies
6
Views
267
GraH
GraH
D
  • Question
Power query filter by last two months and include current month (today) + 1 week
Replies
1
Views
140
JGordon11
JGordon11
A
  • Solved
power query Replace Values - to hyphen or not to hypen?
Replies
5
Views
434
RoryA
RoryA

Forum statistics

Threads
1,215,205
Messages
6,123,634
Members
449,109
Latest member
Sebas8956

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top