Use Power Query Editor Instead dax Column = MAX and AllExcept

[Zakros]

Hi,

I'm a beginner in Power BI & Power Query and I don't manage to do something in Power Query Editor.

In my table "Base DS-Azure" I need to create a new colmun which input the text "Max" on the rows whom the colomunt "DS-Autorization Level" contains the max value (from 0 to 5) , and this, for each data of the column "DS & Mail" (alphabetic column).

I created a new column in my Table with this code :

Max Level =

Var maxValue=

Calculate(

    MAX('Base DS-Azure'[DS-Autorization Level]),

    allexcept('Base DS-Azure','Base DS-Azure'[DS & Mail]))

    return

    If([DS-Autorization Level]=maxvalue,"MAX")

This is perfect but in fact I need this column directly in Power Query Editor.

Could you help me ?

Thanks in advance.

[Zakros]

Hi @tharunkumarRTK 

Thanks a lot for your answer. I managed to adapt it in my use case.

I can continue my process 🙂.

[Zakros]

@ronrsnfld 

Thanks but it's seem a little more complicated for me.

@tharunkumarRTK 

I think I understand this solution. I'll try to adapt it to my use case and I'll inform you... after our easter week end 😉.

Thanks a lot.

[tharunkumarRTK]

@Zakros 
Can you share the pbix file with sample data and desired output?

[Zakros]

Hi @tharunkumarRTK 

Thanks for your interest.

PBI Help 

[ronrsnfld]

Unless you need it for some other purpose, the DS & Mail column is not needed for this Power Query M Code solution:

let

//Change Source line to your actual line
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DS-Name", type text}, {"DS-Autorization Type ", type text}, {"DS-Autorization Level", Int64.Type}, {"Mail", type text}}),
    
//add index column to preserve original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by DS-Name and Mail
//then add column for the MAX
    #"Grouped Rows" = Table.Group(#"Added Index", {"DS-Name", "Mail"}, {
        {"MAX", (t)=> Table.AddColumn(t, "Max Level", 
            each if [#"DS-Autorization Level"] = List.Max(t[#"DS-Autorization Level"]) then "MAX" else null),
            type table [#"DS-Name"=nullable text, #"DS-Autorization Type "=nullable text, #"DS-Autorization Level"=nullable number, 
                        Mail=nullable text, Index=number, Max Level = nullable text]}}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"DS-Name", "Mail"}),
    #"Expanded Max" = Table.ExpandTableColumn(#"Removed Columns", "MAX", {"DS-Name", "DS-Autorization Type ", "DS-Autorization Level", "Mail", "Index", "Max Level"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Max",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

Results from your data

[tharunkumarRTK · Accepted solution]

Here is my solution here 

Table Name: My Solution

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun