Forum Discussion

LithanaM's avatar
LithanaM
Frequent Visitor
4 years ago
Solved

Create IF text column using data comparison (col .ETP) within lines with same ID only (col. NO DISP)

Dear community,  I am trrying to create the "lieu de facturation" column. For one distinct NoDisp, I need to compare the ETP, and facing the highest one write "lieu Principal" and lieu secondaire f...
  • AlexisOlson's avatar
    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.

  • AlexisOlson's avatar
    AlexisOlson
    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.