Help with conditional formulas

[yforti]

I am having difficulty performing a classification based on a status.

Below the data model:

What I need is:

Whenever DS_HISTORICO(header column) contains "Precificado" (marked in yellow), my new column must equal "YES" for ALL my keys, not only for the line that contains "Precificado".

[AlB]

@yforti 

If you paste the sample data you are trying the code on here in text format rather than on a screen cap, i will be able to see where the problem is. Just copy the table (or fragment thereof) and paste it here. The code provided  earlier should work and does in the sample I've tried it on

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[yforti]

@Jimmy801 hello! First of all thanks for help!

Still dont work, here's the result:

What i nedd is: For ALL keys (1509991756755674244933) the result should be  "YES" (For all the lines of this key), because we have at least ONE status "Precificado" in DT_Historico (column), and "NO" for key (15099917246836) cause we dont have the status "Precificado" on the lines of this key.

[Jimmy801 · Accepted solution]

Hello @yforti 

sorry, then I got you wrong (or better... I didn't read your request well :)). Here the adapted code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpJUYrVgTBTU+HMgKLU5My0zOTElHxs0iUlYKYRQtQIVTQtDcwEsoqKYKzSUqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, DS_HISTORICO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"DS_HISTORICO", type text}}),

    final = Table.AddColumn(#"Changed Type", "Res", each if Text.Contains(Text.Lower(_[#"DS_HISTORICO"]), Text.Lower("Precificado")) then true else false, type logical),
    #"Grouped Rows" = Table.Group(final, {"Key"}, {{"AllRows", each _, type table [Key=number, DS_HISTORICO=text, Res=logical]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.AnyTrue([AllRows][Res]) then "Yes" else "No"),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Custom", "AllRows", {"DS_HISTORICO"}, {"DS_HISTORICO"})
in
    #"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AlB]

@yforti 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpJUYrVgTBTU+HMgKLU5My0zOTElHxs0iUlYKYRQtQIVTQtDcwEsoqKYKzSUqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, DS_HISTORICO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"DS_HISTORICO", type text}}),

    list_ = Table.SelectRows(Table.Group(#"Changed Type", {"Key"}, {{"Found", each List.Contains([DS_HISTORICO], "Precificado")}}), each [Found] = true)[Key],
    final = Table.AddColumn(#"Changed Type", "Res", each if List.Contains(list_, [Key]) then "Yes" else "No")
in
    final

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[yforti]

@AlB 

Not yet resolved, see below:



For the key (15099917246836) we dont have the status "Precificado", so the reuslt of Res should be: "no"

[yforti]

@AlB this worked for some cases, in the case where I don't have the status, a column created must contain "No", for example:

this key does not have the status "Precificado"

[Jimmy801]

Hello @yforti 

add a new column with this formula

if Text.Contains(Text.Lower(_[#"DS_HISTORICO"]), Text.Lower("Precificado")) then "Yes" else "No"

here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpJUYrVgTBTU+HMgKLU5My0zOTElHxs0iUlYKYRQtQIVTQtDcwEsoqKYKzSUqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, DS_HISTORICO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"DS_HISTORICO", type text}}),

    final = Table.AddColumn(#"Changed Type", "Res", each if Text.Contains(Text.Lower(_[#"DS_HISTORICO"]), Text.Lower("Precificado")) then "Yes" else "No")
in
    final

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AlB]

Hi @yforti 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMVIrViVYyAjJTUsBMYyAzNRXMNAEyA4pSkzPTMpMTU/LBYqYIaTMgs6REKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, DS_HISTORICO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"DS_HISTORICO", type text}}),


    aux_ = if List.Contains(#"Changed Type"[DS_HISTORICO], "Precificado") then "Yes" else "No",
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New col", each aux_, type text)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers