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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
402
Faisal Yunianto
Faisal Yunianto
K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
220
Kariba
K
C
  • Locked
  • Question Question
Refresh Errors in Power BI Desktop loading queries
Replies
1
Views
110
Fluff
Fluff
CubaRJ
  • Solved
How to replace text in a column based on values of next and previous row?
Replies
7
Views
390
CubaRJ
CubaRJ
I
  • Question Question
Power Query Data types and excel time format issue
Replies
5
Views
372
Anand Sydney
A

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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