Power BI - Count in power query across columns

[rsj88]

Hi,

I have the below dataset

Risk	Risk 2	Risk 3
High	Medium	Low
Medium	Low	Medium

when i create a custom column at the end i need to rank it to show the highest value based on below

1.High,
2.Medium
3.Low

Result:

Risk	Risk 2	Risk 3	Final
High	MEdium	Low	High
MEdium	Low	Medium	Low

thanks in advance
RJ

 

[alansidman]

Why in your results is the second line Low, when obviously Medium is higher ranked.

 

[rsj88]

yeah sorry it should be medium!

thanks

 

[alansidman]

I have a workaround. It consists of replacing the High with 3, Medium with 2 and Low with 1. Then adding a column determining the max of the three columns. Here is the Mcode.

l

et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Risk", type text}, {"Risk 2", type text}, {"Risk 3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","High","3",Replacer.ReplaceText,{"Risk", "Risk 2", "Risk 3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Medium","2",Replacer.ReplaceText,{"Risk", "Risk 2", "Risk 3"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Low","1",Replacer.ReplaceText,{"Risk", "Risk 2", "Risk 3"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each List.Max({[Risk],[Risk 2],[Risk 3]}))
in
    #"Added Custom"

 

[sandy666]

another approach

Risk	Risk 2	Risk 3		Risk	Risk 2	Risk 3	Rank
High	Medium	Low		High	Medium	Low	High
Medium	Low	Medium		Medium	Low	Medium	Medium

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Rank = Table.AddColumn(Source, "Rank", each if List.Count(List.Select(Record.FieldValues(_), each _ = "High")) >= 1 then "High" else if List.Count(List.Select(Record.FieldValues(_), each _ = "Medium")) >= 2 then "Medium" else if List.Count(List.Select(Record.FieldValues(_), each _ = "Low")) >= 3 then "Low" else "Low")
in
    Rank

 

[sandy666]

or even

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Rank = Table.AddColumn(Source, "Rank", each if List.Count(List.Select(Record.FieldValues(_), each _ = "High")) >= 1 then "High" else if List.Count(List.Select(Record.FieldValues(_), each _ = "Medium")) >= 2 then "Medium" else "Low")
in
    Rank

Risk	Risk 2	Risk 3		Risk	Risk 2	Risk 3	Rank
High	High	High		High	High	High	High
High	High	Medium		High	High	Medium	High
High	Medium	Medium		High	Medium	Medium	High
High	Medium	Low		High	Medium	Low	High
High	Low	Low		High	Low	Low	High
Medium	Medium	Medium		Medium	Medium	Medium	Medium
Medium	Medium	Low		Medium	Medium	Low	Medium
Medium	Low	Low		Medium	Low	Low	Low
Low	Low	Low		Low	Low	Low	Low

 

[rsj88]

Hi,

if i wanted this rank for for the last 2 columns of the dataset eg: Risk2 and Risk3. how would be amend the code?

Thanks

 

[sandy666]

you need to change criteria

1.High,
2.Medium
3.Low

to something logical

 

[rsj88]

Ok lets change the logic

I have changed high low medium to
High=1,
Medium=2,
Low=3

Risk1	Risk2	Risk3
1	2	3
1	3	1

would it be easier to get the min number of the last 2 columns in each row?

Thanks

 

[sandy666]

criteria are exactly the same as before
but try custom column: if List.Count(List.Select(List.LastN(Record.FieldValues(_),2), each _ = "High")) >= 1 then "High" else if List.Count(List.Select(List.LastN(Record.FieldValues(_),2), each _ = "Medium")) >= 1 then "Medium" else "Low"