Forum Discussion

marktait's avatar
marktait
Helper I
2 years ago
Solved

Transform Data - add a column count based on another table

Hi. I have a table "DeviceList (2)" which has a column, [Device Name].   I would like (in Transform Data -> Add New Column) to look for the "Device List (2)"[Device Name] within the "Defender SNOW...
Need Help
Tutorial Requests
  • nsexton12's avatar
    nsexton12
    2 years ago

    I think I have figured out your issue. It looks like you did not need the list functions as you could do the operations on the tables themselves. I think using the List function is what was causing the cyclical reference because as soon as I removed them they went away. Please see code below that I think solves your issue: 

     

    if Table.RowCount(Table.SelectRows(
    #"Defender SNOW",
    (row) => Text.Contains( row[Short description],[Device Name])
    )) = 0
    then 0
    else Table.RowCount(Table.SelectRows(
    #"Defender SNOW",
    (row) =>Text.Contains( row[Short description],[Device Name])
    ))

     

    If anyone else can provide a more techincal explanation for why this caused a cyclical reference, please feel free to add on. 

     

    Please mark as a solution if this worked for you!

marktait's avatar
marktait
Helper I
2 years ago

Hi - it says: 

Expression.Error: A cyclic reference was encountered during evaluation.

(I assume this is similar to a circular reference - but I can't see why).

 

Thanks, Mark

nsexton12's avatar
nsexton12
Resolver II
2 years ago

Thank you! Could you post the full Calculated column code? 

  • marktait's avatar
    marktait
    Helper I
    2 years ago

    Hi - yes: 

    = if List.IsEmpty(
        Table.SelectRows(
            #"Defender SNOW",
            (row) => Text.Contains(row[Short description], #"DeviceList (2)"[Device Name])
        )
    ) then 0
    else List.Count(
        Table.SelectRows(
            #"Defender SNOW",
            (row) => Text.Contains(row[Short description], #"DeviceList (2)"[Device Name])
        )
    )
    • nsexton12's avatar
      nsexton12
      Resolver II
      2 years ago

      I think I have figured out your issue. It looks like you did not need the list functions as you could do the operations on the tables themselves. I think using the List function is what was causing the cyclical reference because as soon as I removed them they went away. Please see code below that I think solves your issue: 

       

      if Table.RowCount(Table.SelectRows(
      #"Defender SNOW",
      (row) => Text.Contains( row[Short description],[Device Name])
      )) = 0
      then 0
      else Table.RowCount(Table.SelectRows(
      #"Defender SNOW",
      (row) =>Text.Contains( row[Short description],[Device Name])
      ))

       

      If anyone else can provide a more techincal explanation for why this caused a cyclical reference, please feel free to add on. 

       

      Please mark as a solution if this worked for you!

      • marktait's avatar
        marktait
        Helper I
        2 years ago

        Hi - thank you so much - it does indeed work - although I have no explanation for it either 🙂

         

        Cheers, Mark