• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create Unique Values in Power Query

In Excel, I frequently use the formula =[@Hours] + (0.000000000001 * ROW()) to create unique values for use in MAX and LARGE formulas. I haven't been successful googling an answer (and I may just not be wording my question right), but is there a function or formula in Power Query that will do this same thing? I've just scratched the surface of what all Power Query can do, but I'm learning more all the time - not sure how I lived without it for the last few years!

Thanks!!
YL
 
Question is, do you need to do that over in PQ, since you have the ability to sort lists, (sub)-tables and do a lot more with those?
But here is an idea: row() could be index over in PQ. Add it via the ribbon. Then you just make a calculated column with: [Hours]+ (0.000000000001 * [INDEX]).

EDIT: you might need to pay attention to the data type. I did not test it.
 
While it's possible, like GraH showed.

Think PowerQuery more as staging area for data. Where data is brought in from various sources and transformations applied in order to de-normalize data set(s) in preparation before loading to data model for analysis/calculation.

In your case, add index column (0 or 1 based) to your data in PowerQuery. Then use DAX in data model to perform your calculation.

Depending on Excel version you may not have access to DAX. 2016 does support use of DAX measure without PowerPivot, though somewhat limited in terms of functions that can be used.

In that case, follow GraH's suggestion.
 
Back
Top