M Query Nested IF

[Anonymous]

Hi Team,

Good Morning!

I need M query function for Status column output based on Col 1 to col 3.

Column --> Status is expected output .

Thanks,

KVs

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Thanks to ImkeF, with a little change, it seems to work on my side.

Add custom columns

Custom.1=Record.FieldValues(Record.SelectFields(_, {"Col1", "Col2", "Col3"}))

Custom.2= if Text.Contains([Custom.1], "Yes") then "Yes" else if Text.Contains([Custom.1], "No") then "No" else if Text.Contains([Custom.1],"Pending") then "Pending" else null

After creating "Custom.1", then expand it by "Extract values" with comma.

Whole queries, you could paste it in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJR8ssHEgGpeSmZeelKsTowYayCGGphYvjUYDUPlwiqDqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each Record.FieldValues(Record.SelectFields(_, {"Col1", "Col2", "Col3"}))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values", "Custom.2", each if Text.Contains([Custom.1], "Yes") then "Yes" else if Text.Contains([Custom.1], "No") then "No" else if Text.Contains([Custom.1],"Pending") then "Pending" else null)
in
    #"Added Custom2"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi @Anonymous ,

if you're looking for the first non-empty-item, then adding a column with the following formula should deliver:

List.First(Record.FieldValues(Record.SelectFields(_, {"Col1", "Col2", "Col3"})))