Power Query IF Condition

[Anonymous]

Hi Guys,

Can anyone help me with the below query, Like For the First 3 columns any of two columns contains "OK" then I need Will Fit All Product as "Y", If any one of the column contains -(Negative value) then I need to show it as "N".

Will Fit UNL	WIll Fit SUP	WILL Fit DSL	Will Fit All Product
ok	ok	ok	Y
ok	ok	-534(Values)	N
ok	Ok	- (Null/Blank)	Y

This is I need in Power Query editor

[AntrikshSharma · Accepted solution]

@Anonymous Use this:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText ( "i45Wys9W0oETsTrIArqmxiZoQkqxsQA=", BinaryEncoding.Base64 ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ #"Will Fit UNL" = _t, #"WIll Fit SUP" = _t, #"WILL Fit DSL" = _t ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "Will Fit UNL", type text },
                { "WIll Fit SUP", type text },
                { "WILL Fit DSL", type text }
            }
        ),
    Custom = 
        Table.AddColumn (
            ChangedType,
            "Will Fit All Product",
            each
                let
                    ValuesList = Record.ToList ( _ ),
                    OkCount = 
                        List.Count ( 
                            List.Select ( ValuesList, each _ = "ok" ) 
                        ) >= 2,
                    HasNegative = 
                        List.Count (
                            List.Select (
                                List.Transform ( 
                                    ValuesList, 
                                    each try Number.From ( _ ) otherwise null 
                                ),
                                each _ < 0
                            )
                        ) > 0,
                    Result = if OkCount and not HasNegative then "Y" else "N"
                in
                    Result,
                type text
        )
in
    Custom