Find and replace data by date in power query

Faisal Yunianto

Faisal Yunianto

New Member
Joined
Apr 21, 2023
Messages
13
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
400
Faisal Yunianto
Faisal Yunianto
C
  • Locked
  • Question Question
Refresh Errors in Power BI Desktop loading queries
Replies
1
Views
80
Fluff
Fluff
CubaRJ
  • Solved
How to replace text in a column based on values of next and previous row?
Replies
7
Views
371
CubaRJ
CubaRJ
I
  • Question Question
Power Query Data types and excel time format issue
Replies
5
Views
354
Anand Sydney
A
T
  • Question Question
Power Query - Help!
Replies
3
Views
252
RoryA
RoryA

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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