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!

nsexton12's avatar
nsexton12
Resolver II
2 years ago

If you click on the cell with one of the errors, what does the error message say? If you click in the grey area beside the "Error" hyperlink, it should show at the bottom of the screen.

  • 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])
        )
    )