Forum Discussion
Transform Data - add a column count based on another table
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!
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])
)
)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!
Hi - thank you so much - it does indeed work - although I have no explanation for it either 🙂
Cheers, Mark