Replace record and split field Power Query Excel

[dewandi]

Hi all,

I do power query excel 365 with the result as follow:

My issue is :

1. I want to change "1.15. Institutional Dept" the second row at Office field with " Insti DMA", and "1.15-Institutional Dept" the 5th row at Office field with "Insti Non DMA". What should I do?. 

2. I will spit Office field into 2 side (to remove the numbers in front of the office name). But the problem is if I split it the two last records (Buy and Sell) will be placed at the left side.

Thanks 

[#2]

@dewandi Rather than splitting, perhaps easier to tell PQ which characters are to be kept. Add a custom column and enter:

= Text.Select ( [Office], {"a" .. "z", "A" .. "Z"} )

Now you have a column with only characters from A to Z (both small and caps).

I trust you can do the next steps yourself. Remove the old Office column and rename the Custom one. Now you can also replace "Institutional" in the new column by "Insti".

[#3]

Hi @Riny_van_Eekelen thanks you very much.

I Just try it. 

But something missing. No space between words. 

Not solved yet for issue no.1.

[#4]

@dewandi Ooops, forgot that one. Well, then you can add a space as an allowed character.

= Text.Select ( [Office], {"a" .. "z", "A" .. "Z", " "} )

[#5]

Oh...Ok ..ok...thank you very much.
For issue no 1. If i replace the second row "Institutional" with "DMA" , the 5th row will have same word.
I want for the 5th row as "NON DMA".
Still an issue for me.

[#6 · Accepted solution]

@dewandi Aha, Now I see that you have references to DMA and NON DMA in the rows that have no sales or volume figures. Then you should add another custom column stating:

= if [Sales] = null then [Office] else null

And then Fill down this new column.

Then, I would filter out all rows with no sales and also those with no office name. Then you are left with a clean table of Sales and Volumes for each Office and by date. No need to keep the subtotals as you would generate those when you create reports from this table.

[#7]

Hi @Riny_van_Eekelen thank you very much again.

It's work.

[#8]

@dewandi You're welcome!