Forum Discussion
Solved
Use Power Query Editor Instead dax Column = MAX and AllExcept
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 ...
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
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