Jan 26 2022 09:57 PM
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
Jan 26 2022 10:38 PM
@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".
Jan 27 2022 12:06 AM
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.
Jan 27 2022 12:11 AM - edited Jan 27 2022 12:15 AM
@dewandi Ooops, forgot that one. Well, then you can add a space as an allowed character.
= Text.Select ( [Office], {"a" .. "z", "A" .. "Z", " "} )
Jan 27 2022 12:27 AM
Jan 27 2022 12:52 AM
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.
Jan 27 2022 02:03 AM
Jan 27 2022 12:52 AM
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.