If all all column shows blank for the same row

Power Query
1

Hello,

I have a few columns where a user has the chance to select an option or leave it blank.

image

I want to create a custom column that states “no choice selected” if the value on the other columns (Choice columns) within the same row shows blank data.

2

Hi @supergallagher25,

See if this meets your recuirement, just past it in a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaoAYhCK1YlWMoKwK2B8Y6gcSAjEN0FTb4rGN0PTbw7XD+ZaoBlniabc0ADZvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Choice 1" = _t, #"Choice 2" = _t, #"Choice 3" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.NonNullCount( List.Select( List.RemoveFirstN( Record.ToList(_), 1 ), each _ <> "" )) >0 then null else "no choice selected" )
in
    #"Added Custom"

.

image
image839×249 4.79 KB

.
I hope this is helpful.

1 Like
3

It works. Thank you

1 Like