SOLVED

Creating a powerquery to fix a table

%3CLINGO-SUB%20id%3D%22lingo-sub-1420312%22%20slang%3D%22en-US%22%3ECreating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1420312%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20make%20a%20query%20step%20to%20change%20the%20left%20example%20to%20the%20right%20example.%20The%20problem%20is%20that%20the%20client%20keeps%20sending%20them%20table%20with%20new%20rows%20that%20have%20updated%20results%20rather%20than%20just%20updating%20the%20previous%20row%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENotice%20records%20R779%2C%20R365%20and%20R033%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELeft%20is%20actual%20table%2C%20right%20is%20how%20I%20want%20it%20after%20a%20query%20step%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22704%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3ER654%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E(blank)%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPENDING%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPENDING%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E(blank)%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ER654%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E(blank)%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPENDING%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPENDING%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER779%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ER779%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER779%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ER303%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER303%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EK628%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EK628%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ER635%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER635%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EK640%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER635%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ER033%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ECONFIRMED%3C%2FTD%3E%3CTD%3ECONFIRMED%3C%2FTD%3E%3CTD%3ECONFIRMED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EK640%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ER177%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER033%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3ECONFIRMED%3C%2FTD%3E%3CTD%3ECONFIRMED%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER033%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3ECONFIRMED%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ER177%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3EPENDING%3C%2FTD%3E%3CTD%3E(blank)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1420312%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421466%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421466%22%20slang%3D%22en-US%22%3E%3CP%3E-ERR%3AREF-NOT-FOUND-%40Ocasio27%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20there%20is%20the%20source%20data%20-%20in%20another%20file%20or%20in%20Table%2Frange%20of%20the%20current%20file.%20In%20general%20that's%20doesn't%20matter%2C%20just%20to%20build%20the%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20709px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194813i9343A92D3D629D2D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ELet%20select%20columns%20from%20A%20to%20E%20and%20name%20selection%20as%20Range.%20Query%20it.%3C%2FP%3E%0A%3CP%3EAdd%20index%20column%20to%20the%20table.%3C%2FP%3E%0A%3CP%3ESort%20on%20first%20column%20to%20combine%20same%20ID%3As%20together%2C%20right%20after%20that%20sort%20on%20Index%20in%20descending%20order%20to%20keep%20the%20latest%20records%20first.%3C%2FP%3E%0A%3CP%3EAdd%20one%20more%20index%20column%20to%20fix%20result%20in%20memory%20(or%20wrap%20previous%20step%20with%20Table.Buffer()%20).%3C%2FP%3E%0A%3CP%3ESelect%20first%20column%2C%20Remove%20duplicates.%3C%2FP%3E%0A%3CP%3ERemove%20bot%20columns%20with%20indexes.%3C%2FP%3E%0A%3CP%3ELoad%20result%20back%20into%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422726%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F438859%22%20target%3D%22_blank%22%3E%40Ocasio27%3C%2FA%3E%26nbsp%3BUnless%20I%20have%20misunderstood%20your%20requirement%20isn't%20is%20so%20that%20you%20want%20to%20merge%20records%20for%20non-blank%20and%20updated%20fields%3F%3C%2FP%3E%3CP%3EFor%20instance%2C%20for%20R033%20the%20first%20record%20has%3A%26nbsp%3B%5Bblank%2C%20CONFIRMED%2C%20CONFIRMED%2C%20blank%5D%3C%2FP%3E%3CP%3EThe%20second%20record%20for%20R033%20has%3A%26nbsp%3B%5BNo%2C%20blank%2C%20blank%2C%20CONFIRMED%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20desired%20output%20suggests%20that%20you%20want%20the%20result%20to%20be%3A%3C%2FP%3E%3CP%3E%5BNo%2C%20CONFIRMED%2C%20CONFIRMED%2C%20CONFIRMED%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B's%20solution%20takes%20the%20second%20record%20as%20the%20result%2C%20if%20I'm%20not%20mistaken.%20Inspired%20by%20his%20approach%20of%20double%20indexing%2C%20I%20added%20a%20few%20steps%20that%20unpivots%20the%20table%20first%2C%20and%20re-pivots%20it%20a%20few%20steps%20later%2C%20resulting%20in%20the%20desired%20output%20as%20you%20described%2C%20though%20the%20sorting%20order%20changed%20a%20bit.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-05-28%20at%2008.10.49.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194940i94721925BEE7DF64%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%202020-05-28%20at%2008.10.49.png%22%20alt%3D%22Screenshot%202020-05-28%20at%2008.10.49.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422974%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20exactly%2C%20not%20the%20second%20but%20the%20latest%20appeared%20in%20the%20list.%20Second%20index%20is%20only%20to%20fix%20in%20memory%20in%20UI%20without%20adding%20Table.Buffer%20manually.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422991%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422991%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BTrue!%20Should%20have%20written%20%22latest%20record%22.%20But%20in%20this%20example%20the%20second%20record%20%3D%20the%20latest%20record%2C%20as%20there%20were%20only%20two%20occasions%20of%20R033.%3C%2FP%3E%3CP%3EWill%20have%20to%20look%20into%20the%20Table.Buffer%20as%20I%20haven't%20come%20across%20that%20one%20yet%20in%20my%20discoveries%20of%20PQ.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423000%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BTrue!%20Should%20have%20written%20%22latest%20record%22.%20But%20in%20this%20example%20the%20second%20record%20%3D%20the%20latest%20record%2C%20as%20there%20were%20only%20two%20occasions%20of%20R033.%20I%20would%20have%20written%20%22the%20third%20record%22%20if%20there%20had%20been%20three.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20have%20to%20look%20into%20Table.Buffer%20as%20I%20haven't%20come%20across%20that%20one%20yet%20in%20my%20discoveries%20of%20PQ.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423744%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20possibility%20of%20a%20third%2C%20even%20forth%20record%20(they%20really%20want%20to%20make%20things%20hard%20for%20me).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20the%20code%20work%20anyway%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423993%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20powerquery%20to%20fix%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F438859%22%20target%3D%22_blank%22%3E%40Ocasio27%3C%2FA%3E%26nbsp%3BTested%20it%20with%20your%20example%20data.%20It%20doesn't%20matter%20how%20many%20records%20you%20have%20per%20item.%20However%2C%20once%20a%20field%20has%20received%20a%20value%20other%20than%20(blank)%2C%20the%20current%20query%20does%20not%20allow%20it%20to%20be%20reset%20to%20(blank)%2C%20as%20it%20is%20interpreted%20as%20%22no%20change%22%20compared%20with%20the%20previous%20record.%20Perhaps%20possible%2C%20but%20then%20you'll%20have%20to%20tweak%20the%20query.%26nbsp%3BAnd%20of%20course%2C%20you'll%20still%20have%20to%20adapt%20the%20query%20to%20your%20real%20data%20range%2C%20possibly%20with%20headers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

I am trying to make a query step to change the left example to the right example. The problem is that the client keeps sending them table with new rows that have updated results rather than just updating the previous row 

 

Notice records R779, R365 and R033

 

Left is actual table, right is how I want it after a query step

 

R654(blank)PENDINGPENDING(blank) R654(blank)PENDINGPENDING(blank)
R779(blank)PENDINGPENDING(blank) R779NoPENDINGPENDING(blank)
R779No(blank)(blank)(blank) R303(blank)PENDINGPENDING(blank)
R303(blank)PENDINGPENDING(blank) K628(blank)PENDINGPENDING(blank)
K628(blank)PENDINGPENDING(blank) R635YesPENDINGPENDING(blank)
R635(blank)PENDINGPENDING(blank) K640(blank)PENDINGPENDING(blank)
R635Yes(blank)(blank)(blank) R033NoCONFIRMEDCONFIRMEDCONFIRMED
K640(blank)PENDINGPENDING(blank) R177(blank)PENDINGPENDING(blank)
R033(blank)CONFIRMEDCONFIRMED      
R033No(blank)(blank)CONFIRMED     
R177(blank)PENDINGPENDING(blank)      
7 Replies
Highlighted
Solution

@Ocasio27 

Not sure there is the source data - in another file or in Table/range of the current file. In general that's doesn't matter, just to build the sample

image.png

Let select columns from A to E and name selection as Range. Query it.

Add index column to the table.

Sort on first column to combine same ID:s together, right after that sort on Index in descending order to keep the latest records first.

Add one more index column to fix result in memory (or wrap previous step with Table.Buffer() ).

Select first column, Remove duplicates.

Remove bot columns with indexes.

Load result back into the sheet.

Highlighted

@Ocasio27 Unless I have misunderstood your requirement isn't is so that you want to merge records for non-blank and updated fields?

For instance, for R033 the first record has: [blank, CONFIRMED, CONFIRMED, blank]

The second record for R033 has: [No, blank, blank, CONFIRMED]

 

Your desired output suggests that you want the result to be:

[No, CONFIRMED, CONFIRMED, CONFIRMED]

 

@Sergei Baklan 's solution takes the second record as the result, if I'm not mistaken. Inspired by his approach of double indexing, I added a few steps that unpivots the table first, and re-pivots it a few steps later, resulting in the desired output as you described, though the sorting order changed a bit.

Screenshot 2020-05-28 at 08.10.49.png

 

Highlighted

@Riny_van_Eekelen 

More exactly, not the second but the latest appeared in the list. Second index is only to fix in memory in UI without adding Table.Buffer manually.

@Sergei Baklan True! Should have written "latest record". But in this example the second record = the latest record, as there were only two occasions of R033.

Will have to look into the Table.Buffer as I haven't come across that one yet in my discoveries of PQ.

Highlighted

@Sergei Baklan True! Should have written "latest record". But in this example the second record = the latest record, as there were only two occasions of R033. I would have written "the third record" if there had been three.

 

Will have to look into Table.Buffer as I haven't come across that one yet in my discoveries of PQ.

Highlighted

@Riny_van_Eekelen 

There is a possibility of a third, even forth record (they really want to make things hard for me).

 

Will the code work anyway?

Highlighted

@Ocasio27 Tested it with your example data. It doesn't matter how many records you have per item. However, once a field has received a value other than (blank), the current query does not allow it to be reset to (blank), as it is interpreted as "no change" compared with the previous record. Perhaps possible, but then you'll have to tweak the query. And of course, you'll still have to adapt the query to your real data range, possibly with headers.