SOLVED

Pivot Table Column Item from Power Query Connected Data Not in Numeric Order

%3CLINGO-SUB%20id%3D%22lingo-sub-2539055%22%20slang%3D%22en-US%22%3EPivot%20Table%20Column%20Item%20from%20Power%20Query%20Connected%20Data%20Not%20in%20Numeric%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539055%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20searching%20around%20but%20couldn't%20find%20the%20answer%20to%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20loaded%20to%20my%20data%20model%20and%20connection%20only%20from%20my%20raw%20data%20source%20into%20a%20new%20sheet%20for%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20raw%20data%20I%20have%20an%20order%20number%20of%20how%20things%20should%20be%20grouped%3B%20that%20is%2C%20all%20items%20in%20group%201%20appear%20first%2C%20then%20group%202%2C%20and%20so%20on%20in%20proper%20numerical%20order.%20What%20happens%20instead%20is%20it%20will%20order%20it%20based%20on%20the%20leading%20value%20and%20numerically.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20sum%20my%20data%20and%20using%20the%20first%20column%20this%20order%20%23%20to%20put%20the%20accounts%20in%20proper%20order%20that's%20needed.%20I%20checked%20in%20PQ%20and%20didn't%20notice%20anything%20I%20could%20change%2C%20searched%20around%20and%20didn't%20find%20anything%20that%20could%20affect%20it.%20The%20column%20that%20generates%20this%20value%20is%20a%20formula%20as%20new%20accounts%20get%20added%20every%20now%20and%20then.%20I'm%20not%20sure%20how%20to%20fix%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20from%20my%20data%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-07-12%20at%206.15.43%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F295055iA024A2E04C334FCD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-07-12%20at%206.15.43%20PM.png%22%20alt%3D%22Screen%20Shot%202021-07-12%20at%206.15.43%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2539055%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2539373%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Column%20Item%20from%20Power%20Query%20Connected%20Data%20Not%20in%20Numeric%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20Order%20%23%20are%20texts%2C%20did%20you%20try%20to%20convert%20them%20to%20numbers%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2541326%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Column%20Item%20from%20Power%20Query%20Connected%20Data%20Not%20in%20Numeric%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2541326%22%20slang%3D%22en-US%22%3EThank%20you.%20I%20fixed%20in%20Query%20editor%20by%20changing%20the%20type%20to%20number%20and%20fixed%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20overlooked%20this%20as%20in%20Excel%20it%20worked%20fine%20until%20I%20had%20an%20IFERROR%20that%20added%20a%20blank%20(%E2%80%9C%E2%80%9D)%20and%20changes%20that%20to%20a%20zero%2C%20changed%20the%20type%20to%20a%20number%20with%20decimals%2C%20let%20it%20update%20the%20data%20model%2C%20and%20made%20sure%20on%20PQ%20it%20was%20a%20number%20just%20in%20case.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20the%20help.%20I%20overlooked%20the%20simple%20mistake.%3C%2FLINGO-BODY%3E
Contributor

I've been searching around but couldn't find the answer to this.

 

I loaded to my data model and connection only from my raw data source into a new sheet for use.

 

In my raw data I have an order number of how things should be grouped; that is, all items in group 1 appear first, then group 2, and so on in proper numerical order. What happens instead is it will order it based on the leading value and numerically. 

 

I will sum my data and using the first column this order # to put the accounts in proper order that's needed. I checked in PQ and didn't notice anything I could change, searched around and didn't find anything that could affect it. The column that generates this value is a formula as new accounts get added every now and then. I'm not sure how to fix this.

 

Example from my data:

Screen Shot 2021-07-12 at 6.15.43 PM.png

 

Thank you for any help.

3 Replies
best response confirmed by Jpalaci1 (Contributor)
Solution

@Jpalaci1 

It looks like Order # are texts, did you try to convert them to numbers?

Thank you. I fixed in Query editor by changing the type to number and fixed it.

I overlooked this as in Excel it worked fine until I had an IFERROR that added a blank (“”) and changes that to a zero, changed the type to a number with decimals, let it update the data model, and made sure on PQ it was a number just in case.

Thank you for the help. I overlooked the simple mistake.