Forum Discussion
Create IF text column using data comparison (col .ETP) within lines with same ID only (col. NO DISP)
- 4 years ago
You can do this by using Group By to get the maximal [ETP] per [No Dis] and then merge that back onto your original table and check if [ETP] = [MaxETP].
Sample query you can paste into your Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcfJCQAgDATAXvIOYi6PWoL9tyHsR+L8JpNEhJh6Ezr8NsocU1VMyxZmZtjE3B3bWERg8e9c", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"No Dis" = _t, ETP = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"No Dis", Int64.Type}, {"ETP", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"No Dis"}, {{"MaxETP", each List.Max([ETP]), type nullable number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"No Dis"}, #"Grouped Rows", {"No Dis"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxETP"}, {"MaxETP"}), #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Lieu de facturation", each if [ETP] = [MaxETP] then "Lieu principal" else "Lieu secondaire", type text) in #"Added Custom"Note that since your last two rows are both maximal, it does not break the tie as you did in your example. It's possible to break the tie but makes things more complicated.
- 4 years ago
The first two steps (Source and #"Changed Type") are automatically generated by the query editor from me putting data into the Enter Data tool. You can take your table from wherever it's sourced from and start at the #"Grouped By" step.
I generated all of the steps in this query using the GUI but a couple of them I made small tweaks. The least obvious one is probably where I merge the query with itself in the #"Merged Queries" step. The GUI spits out this code:
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"No Dis"}, #"Grouped Rows", {"No Dis"}, "Grouped Rows", JoinKind.LeftOuter),But I actually want to merge #"Changed Type" with #"Grouped Rows", so I changed the first argument of Table.NestedJoin in that step.
Basically, you can create the whole query with a few button clicks using the tools in the ribbon, except for the tweak mentioned above. The code I gave is more of an example you can follow along with by creating a new query, pasting it into the Advanced Editor, and examining each step in the applied steps pane.
You shouldn't be removing any columns with the approach I suggested since the grouped part gets merged back with the starting table.
FYI, the Power Query language is M rather than DAX. Both are powerful languages but DAX is used for writing measures, not the language of the query editor.
Thank you Alexis, thanks to your explanation, I finally managed to make it and it also helped me better understand how the advanced editor function!