Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating a tagged column based on dynamic columns

Hello,

I have a data set like this, where I want to create the column 'Flag' basis the month columns. If it finds a 'Yes' in any of the columns atleast once then the flag would be 'Yes' If there is No in all the 6 columns then the flag would be 'No'. The difficulty here is, the month columns will keep on updating, for example now sitting in Feb I have columns from Jan-Sept, moving to March I will have Feb-Oct. So I need to keep the query with respect to an index of the column. How can I do this? Please help

 

TIA

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous,

    I'd like to suggest you unpivot these fields to attribute and value, then you can simply write Dax formula to compare across all attributes and return tags.

    Unpivot columns (Power Query) 

    In addition, if you not want to change the default table structure, you can add a custom column on query editor site to compare the values of each field:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", each if List.Count(List.Select(Record.ToList(Record.RemoveFields(_,{"Customer"})), each _="Yes"))>0 then "Yes" else "No")

    Regard,

    Xiaoxin Sheng