Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
marktait
Helper I
Helper I

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"[Short description] field and count how many results it finds.

The formula below is accepted in the Custom Column editor - but then shows as an error when viewing the table in PBI (see screenshot below the formula).

 

Any ideas what I may be doing wrong?

 

Thanks for any help,

 

Mark

 

 

 

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

 

Capture.PNG

 

1 ACCEPTED SOLUTION

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!

View solution in original post

6 REPLIES 6
nsexton12
Resolver II
Resolver II

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.

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

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.