[Power Query] Extracting from multiple columns into a new column

**Bleucherie** · 02-18-2024, 09:26 AM

Good morning, all! I am currently working on setting up a template for importing purposes as my organization will soon be switching from one banking platform to another and the reports I download are not in the same format and the column information doesn't line up for each transaction type consistently. I have included a sample workbook with the data, a power query setup and the desired look I would want it to be shown. A few things I am trying to achieve:

-From the Details tab I want to extract the text after "ORIG CO NAME=" from columns M, N and O into a new column. Currently have it where it is transforming the columns, but I lose the other data which I don't want.

-Where I did the Insert Text between Delimiters, I want to be able to have it where if there isn't a transaction number within the delimiter that it will give me the bank reference number (Column K).

-I am looking to change the signs in the Amount (Column E) based on the information in Column F so if a row has CR it stays positive and if it has DR is changes to a negative number.

My Table is all over the place but I'll clean it up once I have the above steps so I do that part last to get it to the desired results tab. Thank you all for your help.

**DJunqueira** · 02-18-2024, 01:01 PM

Good Morning.

I did 1º and 3º as columns '- Amount' and 'ORIG CO NAME', the 2º one I would like to see expected result if possible.

**Bleucherie** · 02-18-2024, 01:52 PM

Woops I just realized I attached the wrong file, so I have edited my post to include the correct one and you can see the Desired Results tab to show how it would end up looking like.

**Bleucherie** · 02-19-2024, 05:09 PM

Originally Posted by DJunqueira

Good Morning.

I did 1º and 3º as columns '- Amount' and 'ORIG CO NAME', the 2º one I would like to see expected result if possible.

I have corrected the file I attached so you can see the desired results tab.

**DJunqueira** · 02-19-2024, 06:17 PM

We are close, but columns 'Transaction Type' that seems to me to be the one that you want to help filter 'Bank Reference' only change for "CHECK" and the result don't look as your column 'Transaction Number' in tab 'Desired Results'.
Also check if tab Table1 is close to what you are looking for.
I'm hesitant to proceed because I'm not sure of the final result or the columns I should use.

**Bleucherie** · 02-19-2024, 09:03 PM

Originally Posted by DJunqueira

We are close, but columns 'Transaction Type' that seems to me to be the one that you want to help filter 'Bank Reference' only change for "CHECK" and the result don't look as your column 'Transaction Number' in tab 'Desired Results'.
Also check if tab Table1 is close to what you are looking for.
I'm hesitant to proceed because I'm not sure of the final result or the columns I should use.

So, in the tab 'Desired Results' the "Transaction Number" column I had set up a text between delimiter step to get the TRN number from the columns "REMARK 02" and "REMARK 03":

Attachment 860192

Since not all of the EFT CREDIT transactions have that TRN # I wanted to get the Bank Reference Number for those. It is fine that the CHECKS transactions are blank as I just added those lines to check whether the Amount would change signs and are not important for the import process that I do.

Thank you for your help on this! Table1 looks pretty close to the Desired Tab, I would just eliminate the Customer Reference column since it isn't needed.

**DJunqueira** · 02-19-2024, 10:39 PM

In the column 'Transaction Number' I need to change some cell values, but it is ok.

Formula:

Please Login or Register  to view this content.

[Power Query] Extracting from multiple columns into a new column

LinkBack

Thread Tools

Rate This Thread

Display

[Power Query] Extracting from multiple columns into a new column

Re: [Power Query] Extracting from multiple columns into a new column

Re: [Power Query] Extracting from multiple columns into a new column

Re: [Power Query] Extracting from multiple columns into a new column

Re: [Power Query] Extracting from multiple columns into a new column

Re: [Power Query] Extracting from multiple columns into a new column

Re: [Power Query] Extracting from multiple columns into a new column

Thread Information

Users Browsing this Thread

Similar Threads

Power Query: Split Column into two columns by every other row

[SOLVED] [Power Query] How to filter 0 from two columns in power query editor in one step?

[SOLVED] Power Query Transform multiple columns

[SOLVED] Power Query Extracting Data from Multiple Websites

[SOLVED] Ranking in Power Query via multiple columns

Custom Permutations Column in Power Query from two columns in the same Query

[SOLVED] Power Query ranking multiple columns

Bookmarks

Bookmarks

Posting Permissions