Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

create custom column using one column as a list

I have a table with two columns Submitter email and Manager email.  How would I create a custom column which will display TRUE if the submitter email is found anywhere in the Manager email.    as sh...
Need Help
  • goncalogeraldes's avatar
    goncalogeraldes
    4 years ago

    Hello there Anonymous ! You can check if this works for you:

    Leader of Leader =
VAR _sub =
    SELECTEDVALUE ( [submitter email] )
VAR _list =
    DISTINCT ( [Manager email] )
RETURN
    IF ( _sub IN _list, TRUE (), FALSE () )

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

KNP's avatar
KNP
Icon for Super User rankSuper User
4 years ago

In Power Query (Transform data) paste the below code into the 'Advanced Editor' of a blank query.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WSs1NzMxRMFTSgbIslGJ1YKJGcFFLJFFjBbiwIZKwCVzUSCk2FgA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"submitter email" = _t, #"Manager email" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"submitter email", type text}, {"Manager email", type text}}
  ),
  list = #"Changed Type"[Manager email],
  Custom1 = Table.AddColumn(#"Changed Type", "custom", each List.Contains(list, [submitter email]))
in
  Custom1

Hope this helps.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I feel like I am missing something here. Like identifying the table I am pulling the new columns from. 

    so how do I make this happen from an existing table in my report.  

    For me the data is in the All Employee Table

    • KNP's avatar
      KNP
      Icon for Super User rankSuper User
      4 years ago

      Can you show me the structure of the table in your report (without any sensitive data)?

      And paste your existing Power Query code for that query?

      I don't quite follow what you mean.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I have not shared previously so not sure what you are asking for exactly.  below is a screen shot of the report.   the two colums exist in the All Employees table.   the Leader of leaders table was the blank table I created and put the code into. but the code only built my example table again. 

         

        i need it to build from the All employees table so when it is update the leader of leader table updates as well.