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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Antozas
New Member

to match an exact string and return another item from a table or datas

Hello Everyone,
 
I am new user to power query . My work involve comparing data. I have to manually replace text in data transactions every day which takes a lot of time.
 
I have to prepare  the information which appears in column G through find and replace formula. I am aware that through power query you can extract and transform the data the way I want in column G.for thousand rows. 
 
I want to replace the text that appears in the B Column known as description to be replaced by the text that needs to be appears in the G Column. It involve some rules if condition matches then replace with another.
 
Also, I want to learn more on power can any one suggest recommended books/material. 
 
Below is gthe summary of information:
 
DateDetailsTransaction TypeDebitCreditBalanceNew Details 1Criteria
10/05/2023BUSINESS DEPOSIT 1925 - DEPOSIT 42.3462752.61if text matches "BUSINESS DEPOSIT" replace null and give text to the right any number of chacracters
02/05/2023BUSINESS DEPOSIT 41125411925 - DEPOSIT 78.531227892.8841125411if text matches "BUSINESS DEPOSIT" replace null and give text to the right any number of chacracters
01/06/2023COPY DOCUMENT CHARGE698 - MISCELLANEOUS FEES30 1407868.3COPYDOCUMENT ,if textmatches "COPY DOCUMENT" replace null and keep information to right CHARGE
24/05/2023ACCOUNT ADJUSTMENT BNED:hul330 :240523920 - BALANCE ADJUSTMENT - CREDIT 0.03136576.13hul330 :240523if text matches "ACCOUNT ADJUSTMENT BNED:" replace null and anyremaining text to the right
24/05/2023ACCOUNT ADJUSTMENT BNED:94017389 :240523920 - BALANCE ADJUSTMENT - CREDIT 1136577.1394017389 :240523if text matches "ACCOUNT ADJUSTMENT BNED:" replace null and anyremaining text to the right
05/05/2023TRANSFER TO HMRC VAT WITH EXCHANGE RATE 1.00000. REF: 0015 6648 696 1657310960 - BALANCE ADJUSTMENT - DEBIT622490.42 24586.87TRANSFERTOHMRC ,if text matches TRANSFER TO HMRC VAT WITH EXCHANGE RATE 1.00000. REF: 0015 6648 696 1657310replace null and give TRANSFERTOHMRC ,
02/05/2023ACCOUNT ADJUSTMENT BNED:940120550 :020523960 - BALANCE ADJUSTMENT - DEBIT20 1222890.9940120550 :020523if text matches ACCOUNT ADJUSTMENT BNED:940120550 :020523replace null and give 940120550 :020523
02/05/2023ACCOUNT ADJUSTMENT BNED:940120547 :020523960 - BALANCE ADJUSTMENT - DEBIT20 1222870.9940120547 :020523if text matches ACCOUNT ADJUSTMENT BNED:940120547 :020523replace null and give 940120547 :020523
30/05/2023TRANSFER VIA FASTER PAYMENT TO All Aspex REFERENCE All Apex962 - BANK TRANSFER - DEBIT350 450136.66FASTER PAYMENT ,if text matches TRANSFER VIA FASTER PAYMENT TO All Aspex REFERENCE All Apexreplace null and give FASTER PAYMENT ,
09/05/2023TRANSFER VIA FASTER PAYMENT TO SHAHID HUSSAIN ARCHITECTS LTD REFERENCE Shahid Hussain962 - BANK TRANSFER - DEBIT4219.2 137642.13FASTER PAYMENT ,if text matches TRANSFER VIA FASTER PAYMENT TO SHAHID HUSSAIN ARCHITECTS LTD REFERENCE Shahid Hussainreplace null and give FASTER PAYMENT ,
16/05/2023CHARGES FROM964 - CHARGES - DEBIT1086 293135.58CHARGES ,if text matches CHARGES FROMreplace null and give CHARGES ,
16/05/2023CHARGES FROM964 - CHARGES - DEBIT536.58 1319697.1CHARGES ,if text matches CHARGES FROMreplace null and give CHARGES ,
30/05/2023DIRECT DEBIT PAYMENT TO BOOKER DIRECT LTD REF 10344650, MANDATE NO 0021966 - DIRECT DEBIT105123.21 182653.55BESTFOODS ,if text matches DIRECT DEBIT PAYMENT TO BOOKER DIRECT LTD REF 10344650, MANDATE NO 0021replace null and give BESTFOODS ,
19/05/2023DIRECT DEBIT PAYMENT TO WORLDPAY REF AO3577 254629577, MANDATE NO 0022966 - DIRECT DEBIT55.36 506699.58WORLDPAY 254629577if text matches DIRECT DEBIT PAYMENT TO WORLDPAY REF AO3577 254629577, MANDATE NO 0022replace null and give WORLDPAY 254629577
04/05/2023BANK GIRO CREDIT REF TACO TIME LIMI3004, HULL923 - BANK TRANSFER - CREDIT 1373.91479740.643004, HULLiftext matches  "BANK GIRO CREDIT REF TACO TIME LIMI" replace null and keep any information to right
04/05/2023BANK GIRO CREDIT REF TACO TIME LTD 0105, ECOMM923 - BANK TRANSFER - CREDIT 1141.41485516.570105, ECOMMif text matchest"BANK GIRO CREDIT REF TACO TIME LTD " replace null and keep any information to right
11/05/2023FASTER PAYMENTS RECEIPT REF.NOT PROVIDED FROM Roofoods Limited923 - BANK TRANSFER - CREDIT 50105.3146951.47Deliveroo ,if text match "FASTER PAYMENTS RECEIPT REF.NOT PROVIDED FROM Roofoods Limited" replace with Deliveroo ,
05/05/2023BANK GIRO CREDIT REF JUST EAT.CO.UK ACC, JEA12082894-571632923 - BANK TRANSFER - CREDIT 1775.6359651.02JustEats ,if text match "BANK GIRO CREDIT REF JUST EAT.CO.UK ACC, JEA12082894-571632" replace with Just eats ,
17/05/2023BANK GIRO CREDIT REF UBER PAYMENTS UK L, GOR5W156Z5MW923 - BANK TRANSFER - CREDIT 2285.47242921.45Uber ,if text match"BANK GIRO CREDIT REF UBER PAYMENTS UK L," repalce with Uber ,
09/05/2023FASTER PAYMENTS RECEIPT REF.NOT PROVIDED FROM 23296384 0405923 - BANK TRANSFER - CREDIT 48.72126247.4723296384 0405if text match "FASTER PAYMENTS RECEIPT REF.NOT PROVIDED FROM " repalce with null to get information to right
16/05/2023BANK GIRO CREDIT REF NEST PENSION PMT, P230511DC4048986923 - BANK TRANSFER - CREDIT 204.6312492.9NESTPENSION PMTif text matches "BANK GIRO CREDIT REF NEST PENSION PMT, P230511DC4048986" to give new text "NEST PENSION MT"
04/05/2023BANK GIRO CREDIT REF CARD TXNS 300423, 47150251923 - BANK TRANSFER - CREDIT 1719.58565457.78300423, 47150251IF text match "BANK GIRO CREDIT REF CARD TXNS" repalce with null and give information after right any number of chacracters
06/06/2023BANK GIRO CREDIT REF TACO BELL ABER0106, 21289194923 - BANK TRANSFER - CREDIT 206.461556133.03ABER0106, 21289194if text matches "BANK GIRO CREDIT REF TACO BELL" replace null and any text to the right any number of chacracters
04/05/2023BANK GIRO CREDIT REF TACO BELL GLAS0105, 21289354923 - BANK TRANSFER - CREDIT 2168.451425374.02GLAS0105, 21289354if text matches "BANK GIRO CREDIT REF TACO BELL" replace null and any text to the right any number of chacracters
 
Best Regards,
 
Antonio
 
 
2 REPLIES 2
Antozas
New Member

Hello There,

 

Thanks for reply and information. It was amazing, Coulc you please share link to learn more on power query videos on thsi wesbite.

 

 

Thank you

 

Antozas 

some_bih
Super User
Super User

Hi @Antozas start with link below

https://support.microsoft.com/en-us/office/add-a-conditional-column-power-query-f2422ed1-f565-4e64-b... 

Hope this help, kudos appreciated





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.