ICAEW.com works better with JavaScript enabled.
Exclusive

Doing the impossible with Power Query

Author: nomiS tsruH

Published: 15 Oct 2021

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.
As a follow up to a recent article on reversing text with Excel Simon shows another, potentially more straightforward method using Power Query.

yreuQ rewoP htiw elbissopmi eht gnioD

:alumrof eht gnitartsnomed dna gninialpxe ,txet esrever ot desu eb deedni dluoc snoitcnuf lecxE gnitsixe woh wohs ot no tnew maiL .edoc gnimmargorp fo tros emos fo esu eht tuohtiw lecxE ni elbissopmi neeb ylsuoiverp dah txet gnisrever taht detressa dah maet hcraeseR tfosorciM ehT .noitcnuf )(ADBMAL wen s'lecxE fo rewop eht fo elpmaxe na sa gnirts txet a esrever ot ytiliba eht desu taht elpmaxe hcraeseR tfosorciM a ta dekool kcitsaB maiL ,elcitra tnecer a nI

))1,1+)))1A(NEL&":1"(TCERIDNI(WOR-)1A(NEL,1A(DIM(TACNOC=

.yreuQ rewoP gnisu dohtem drawrofthgiarts erom yllaitnetop ,rehtona gniwohs htrow saw ti hguoht I ,ssenetelpmoc rof tsuJ

:evitisnes esac si yreuQ rewoP sa esac txet htiw luferac gnieb ,alumrof gniwollof eht ni epyt ot deen neht eW .nmuloC motsuC gnisu dna puorg lareneG ,bat nobbiR nmuloC ddA eht ot gniog yb yreuq ruo ot pets elgnis a dda ot deen eW .rotide yreuQ rewoP eht otni elbaT ruo daol ot '…teehS morf ataD teG' esoohc dna elbaT ruo ni kcilc-thgir neht nac eW .esrever ot tnaw ew taht txet eht gniniatnoc wor atad elgnis a dna txeTyrtnE dedaeh nmuloc elgnis a htiw elbaT lecxE na etaerc dluoc ew ,llec lecxE elgnis a otni deretne txet eht esrever ot tnaw ew taht gnimussA

)]txeTyrtnE[(esreveR.txeT =

.]txeTyrtnE[ retne ot golaid nmuloC motsuC eht ni tsil 'snmuloc elbaliavA' eht ni gnidaeh nmuloc ruo kcilc-elbuod nac ew ,)etelpmoCotuA esu ro( stekcarb dnuor eht dna esreveR.txeT ni epyt ot deen thgim ew hguohtlA

.nmuloc dnoces eht ni txet desrever eht dna nmuloc tsrif eht ni eb lliw txet lanigiro ruo erehw teehskrow lecxE na otni kcab elbaT ruo daol ot daoL & esolC esu ot deen tsuj eW .ti s'tahT
.tuo melborp eht tros ot syaw eerht evah won uoy ,sdrawkcab ,elcitra eritne na deedni ro ,hpargarap a nettirw yllatnedicca evah uoy taht dnif od uoy fi ,oS

In a recent article, Liam Bastick looked at a Microsoft Research example that used the ability to reverse a text string as an example of the power of Excel's new LAMBDA() function. The Microsoft Research team had asserted that reversing text had previously been impossible in Excel without the use of some sort of programming code. Liam went on to show how existing Excel functions could indeed be used to reverse text, explaining and demonstrating the formula:

=CONCAT(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

Just for completeness, I though it was worth showing another, potentially more straightforward method using Power Query.

Assuming that we want to reverse the text entered into a single Excel cell, we could create an Excel Table with a single column headed EntryText and a single data row containing the text that we want to reverse. We can then right-click in our Table and choose 'Get Data from Sheet…' to load our Table into the Power Query editor. We need to add a single step to our query by going to the Add Column Ribbon tab, General group and using Custom Column. We then need to type in the following formula, being careful with text case as Power Query is case sensitive:

= Text.Reverse([EntryText])

Although we might need to type in Text.Reverse and the round brackets (or use AutoComplete), we can double-click our column heading in the 'Available columns' list in the Custom Column dialog to enter [EntryText].

That's it. We just need to use Close & Load to load our Table back into an Excel worksheet where our original text will be in the first column and the reversed text in the second column.
So, if you do find that you have accidentally written a paragraph, or indeed an entire article, backwards, you now have three ways to sort the problem out.