Hi,I have this column which I use for scoring/ranking across varius type of FRUIT sold. It works really well, I'm moving into a bigger data set and I want to see if I can move this directly into power query... How do I write the code in the 'add column in power query? - is it at all possible? COLUMN NAME = VAR NCT = CTL[FRUIT Code]VAR filteredTable = FILTER ( CTL, NOT ( ISBLANK ( CTL[(PQ) PSM] ) ) && CTL[FRUIT Code] = NCT)RETURNIF(ISBLANK(CTL[(PQ) PSM]),BLANK(),DIVIDE (RANKX (filteredTable,CTL[(PQ) PSM],,ASC) - 1,COUNTROWS ( filteredTable ) - 1))*100

Hi KristofferAJ ,The most interesting think in your measure is the rank. I have never seen that done in Power Query. Here is what I found, and it is very cool. https://data-witches.com/2020/10/21/adding-a-row-rank-based-on-a-different-column-with-power-query/ The picture below is daxformatter.com and I would highly recommend this to you. I will send you my pbix. The second issue is counting the number of rows by fruit, which is simply using groupby in Power Query. Let me know if you have any questions.If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.Nathaniel

Convert DAX column into power query (add column)

6 Replies

ronrsnfld
Super User
4 years ago
Take a look at Convert DAX column into Power Query (add column)
Seems to be the same question.
Nathaniel_C
Community Champion
4 years ago
Hi KristofferAJ

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you.

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel
- KristofferAJ
  Helper III
  4 years ago
  Hi Nathaniel_C
  Thanks for your interest. I have made a dummy pBIX with the formula as I have it now, my wish is to have the "COLUMN NAME" directly in powerquery preferebly by 'add column'
  
  I have placed the file here, as I can't see an ooption to upload here..
  
  https://www.dropbox.com/s/trrdggjz4c1x3ff/Test_scoringrank_in_powerquery.pbix?dl=0
  
  Thanks
  Kristoffer
  - Nathaniel_C
    Community Champion
    4 years ago
    Hi KristofferAJ ,
    The most interesting think in your measure is the rank. I have never seen that done in Power Query. Here is what I found, and it is very cool.
    
    https://data-witches.com/2020/10/21/adding-a-row-rank-based-on-a-different-column-with-power-query/
    
    The picture below is daxformatter.com and I would highly recommend this to you.
    
    I will send you my pbix. The second issue is counting the number of rows by fruit, which is simply using groupby in Power Query.
    
    Let me know if you have any questions.
    
    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel
Anonymous
Not applicable
4 years ago
Hi KristofferAJ ,

Ranking in the power query is easy, here's the video for reference.

1.According to the tutorial in the video above, get the [Rank] column.

2.Add a custom column to count rows.

3.Expand the [Custom] column.

4.Add a custom column to get the results.

You can check more details from my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Test_scoringrank_in_powerquery.pbix42 KB