Power Query - Without using IF function

[KishorKK]

Hi All,


I am learning power query and i would like to know that how to get results and the grades in power query. Can any one explain me how to do. In normal i can go with IF but using power query how to do that?

 

[Chihiro]

So what's your expected result and/or normal calculation?

 

[KishorKK]

I would like to see the result like "Pass" or "Fail". and grades like "A", "B" etc.,

I have attended a session but very hard to remember. Can you please help with this? Thanks

 

[Chihiro]

Pass, Fail is easy to do with M function. You'd add custom column in PowerQuery using "IF". However, do note that syntax is different from Excel formula.

But what range is Pass and Fail? I need that to give you formula.

If multiple criteria with over 3 conditions, like "A~F" grade scale. There's 2 ways to do it.

1. Create 2nd table with complete score range with corresponding grade. Join (i.e. merge) 2 tables based on score.

2. Set up custom banding/bucket function.

Basic steps for creating banding function is...

1. Create new blank query and enter advanced editor
2. Copy and paste code below and modify bucket as needed
3. Click OK and exit
4. Name the function (Ex. "ScoreBucket")

(score) =>
let GRDBucket =
    {
        {(x)=>x<60, "F"},
        {(x)=>x<70, "D"},
        {(x)=>x<80, "C"},
        {(x)=>x<90, "B"},
        {(x)=>true, "A"}
    },
    Result = List.First(List.Select(GRDBucket, each _{0}(score))){1}
in
    Result

Then you'd go to your query and add custom column.
Formula Syntax =ScoreBucket([YourScoreColumn])

 

[Chihiro]

Ah, found the article that explains the custom function method in detail.

http://www.excelguru.ca/blog/2016/02/29/creating-a-banding-function-in-power-query/

 

[KishorKK]

Thanks man.. But there is no need to give any formula just we need to give the range what we like to use.. That's it. I will letting you know that once i asked with trainer.

 

[Chihiro]

Ah, your Sub names are "if2" etc. You can't use that as that's like cell reference and there will be name conflict.

So, change it to F_if2 or some other name and you can assign it.

 

[AVK]

Thanks man.. But there is no need to give any formula just we need to give the range what we like to use.. That's it. I will letting you know that once i asked with trainer.

Plz refer attach file.

 

[Chihiro]

Ah, your Sub names are "if2" etc. You can't use that as that's like cell reference and there will be name conflict.

So, change it to F_if2 or some other name and you can assign it.

Woops, this post was meant for another thread...

http://chandoo.org/forum/threads/assigning-a-button.32172/#post-191103