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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jared_Leonard
Frequent Visitor

Why Does this Query Produce a Cyclic Reference

I am trying to filter data Power BI Desktop brings in using Power Query, but the query I've made causes a cyclic reference and am unsure why. Here is the Power Query M text:

let
    Source = CommonDataService.Database("mydatasource.dynamics.com"),
    dbo_responses = Source{[Schema="dbo",Item="responses"]}[Data],
    Walkies = Table.SelectRows(dbo_responses, each [surveyidname] = "Off Lease Inspection" and [surveyanswer] = "Walkie Inspection"),
    All_Walkies = Table.SelectRows(responses, (e) => Table.Contains(Walkies, [responsesid = e[responsesid]]))
in
    All_Walkies

 

What I am trying to do is get all rows from "responses" that have some row with a [responsesid] containing a "Walkie Inspection" value in [survey_answer]. So, if any row has [responsesid] = 123 and [survey_answer] = "Walkie Inspection", grab all rows from "responses" with [responsesid] = 123.

 

The "Walkies" step seems to work fine, it contains only rows with ID's that have a "Walkie Inspection" response. Then in the "All_Walkies" step, I select all rows from responses where that row has an ID that's in "Walkies". It seems resonable to me, but Power Query Editor disagrees.

 

Advice?

2 REPLIES 2
Anonymous
Not applicable

I assume that the "Responses" table is another table/query already in Power Query.  If so, try this instead:

 

All_Walkies = Table.AddColumn(Walkies, "ResponsesId", each Table.FindText(responses, [responsesid]))
in
All_Walkies

 

Table.FindText will return the whole row in the other table if it contains your text in any field. It's like a fuzzy Vlookup. Sometimes the syntax can trip me up, so play around with it.

 

--Nate

Jared_Leonard
Frequent Visitor

Ok, stupid problem, I had 

Table.SelectRows(responses, (e) => Table.Contains(Walkies, [responsesid = e[responsesid]]))

when I should have had

Table.SelectRows(dbo_responses, (e) => Table.Contains(Walkies, [responsesid = e[responsesid]]))

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.