• 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.

Power Query - Without using IF function

KishorKK

Member
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?
 

Attachments

  • Results.xlsx
    19 KB · Views: 7
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
 
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")
Code:
(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])
 
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.
 
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.
 
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.
 

Attachments

  • Results.xlsx
    17.4 KB · Views: 5
Back
Top