Forum Discussion

Jared_Leonard's avatar
Jared_Leonard
Frequent Visitor
4 years ago

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?

Need Help

2 Replies

  • Jared_Leonard's avatar
    Jared_Leonard
    Frequent Visitor
    4 years ago

    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]]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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