Convert DAX column into power query (add column)

[KristofferAJ]

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)

RETURN

IF(ISBLANK(CTL[(PQ) PSM]),BLANK(),

DIVIDE (

RANKX (

filteredTable,

CTL[(PQ) PSM],

,

ASC

) - 1,

COUNTROWS ( filteredTable ) - 1

))*100

[ronrsnfld]

Take a look at Convert DAX column into Power Query (add column)

Seems to be the same question.

[v-stephen-msft]

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.

[Nathaniel_C]

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]

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 · Accepted solution]

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

[Nathaniel_C]

PBIX https://1drv.ms/u/s!AgCd7AyfqZtE4ShMLbd1zhJPNDvX?e=kjNvFh