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

[LithanaM]

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 for the others. See the table below with a short example. 

Is there a way to do this?

Thank you for your help on this matter!

Have a great day,

Nathalie

[AlexisOlson · Accepted solution]

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 · Accepted solution]

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.

[LithanaM]

Thank you Alexis for this very fast response. I am amazed by the capacity of PowerBi (and how you handle its programmation!).

I am however a real beginner (started 3 months ago with no previous expertise...).

Could you help me decipher a little bit more the program you created? I need to adapt it to my real data, and I am struggling...

My questions : 

- where should I add this program? with creating a new column?

-I am a bit at loss with the first part of the source, which i suppose in the program you wrote is where the table was in your case and the need to transform it? Is there a need to add the source in my program?

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcfJCQAgDATAXvIOYi6PWoL9tyHsR+L8JpNEhJh6Ezr8NsocU1VMyxZmZtjE3B3bWERg8e9c", BinaryEncoding.Base64), Compression.Deflate)),

-  Can I start directly with  the _t your created?

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"No Dis" = _t, ETP = _t]),

- I suppose I don't need to do the change type as my column are already as you define them in the program. 

I would be very grateful if you could answer these somewhat basic questions! (the learning curve is steep, but I am really willing! :)) 

[AlexisOlson · Accepted solution]

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.

[LithanaM]

thank you for your answer.

I believe I am too much of a beginner to apply this 😉 I have much more columns than the ones I gave as an example, and I actually need to keep a lot of them (I have for example several years). It did help me though to better undertand some of the programmation, thank you so much!

I'll have to take a full course on DAX to better understand all the technicality. PowerBi is an amazing tool, but to keep the size reasonnable (my data counts actually a huge amount of lines), I need to go through this huge steep learning curve!

[AlexisOlson]

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.

[LithanaM]

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!