Query List Parameter causing "references other queries or steps...Rebuild Data Combination" Error

[akwAUBI]

Hi all,

I was working to apply a date parameter to a stored query but the date values, because they are supplied by a separate List Query, are causing the dataset query to throw the following error.

"Formula.Firewall: Query '[queryname]' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

Prior to posting this I reviewed Similar Solved Forum Post and a separate link that it referenced and came away with a general understanding of the M query execution precidence and the resoluiton being a matter of nesting the upstream query among the dependant downstream query.

I was looking to get some assistance in restructuring my query interaction because I believe that the introduction of a parameter in to the mix makes things slightly more tricky.

Consider the following dependency chain:

1. Query data set for DimDate.Date values.
2. Convert 'date' column to List Query
3. Create parameter 'ReportingDate' based on List query
4. Pass Reporting date value to separate query, to fetch desired data.

Appropriately nesting steps 1-3 into the 'M' syntax for step 4 is what I'm looking for insight on.

M query snippets:

1. Query data set for DimDate.Date values.
   

   let
   Source = AnalysisServices.Database(ServerInstance, Database, [Query="WITH MEMBER [Measures].Dummy AS 1 SELECT {[Measures].Dummy} ON Columns, {[Month End Date].[Date].[Date].Members} ON ROWS FROM [CUBE]"]),
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"[Month End Date].[Date].[Date].[MEMBER_CAPTION]", type date}})
   in
   #"Changed Type"

2. Convert 'date' column to List Query
   

   let
       Source = AnalysisServices.Database(ServerInstance, Database, [Query="WITH MEMBER [Measures].Dummy AS 1 SELECT {[Measures].Dummy} ON Columns,  {[Month End Date].[Date].[Date].Members} ON ROWS FROM [CUBE]"]),
       #"Renamed Columns" = Table.RenameColumns(Source,{{"[Month End Date].[Date].[Date].[MEMBER_CAPTION]", "Date"}}),
       #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
       Date1 = #"Changed Type"[Date]
   in
       Date1

3. Create parameter 'ReportingDate' based on List query
   

   #date(2016, 1, 1) meta [IsParameterQuery=true, ExpressionIdentifier=DimDate, Type="Date", IsParameterQueryRequired=true]

4. Pass Reporting date value to separate query, to fetch desired data.
   

   let
       Source = AnalysisServices.Database(ServerInstance, Database, [Query=
       " SELECT { } ON ROWS,
       {[DimDate].[Month].[Month].Members}
       FROM ( SELECT ( { [DimDate].[Year Quarter Month].[Month].&["& Number.ToText(Date.Year(ReportingDate)) &"]&["& Number.ToText(Date.Month(ReportingDate)) &"] } ) ON COLUMNS FROM [CUBE])
       WHERE ( [DimDate].[Year Quarter Month].[Month].&["& Number.ToText(Date.Year(ReportingDate)) &"]&["& Number.ToText(Date.Month(ReportingDate)) &"] ),
       #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", **Etc.**
   in
       #"Changed Type"

Notice that the date parameter selection is passed into the final query with Number.ToText(Date.Month/Year()) functions.

Granted this is more of a mouthful of a question than I'd like, help in seating these M query parts into a single pass that power BI will evaluate and accept would be greatly appreciated.

Thanks

[Anonymous]

@akwAUBI not sure if it will help you but I faced the same issue and after doing some research, I was able to fix it but it works only in on-premises data gateway (personal mode). I was able to resolve this issue by making the below changes.

• Setting the correct Privacy level
• Enable the Fast Combine option in Personal Gateway

Refer to this link for more information. https://simplebiinsights.com/power-bi-issue-in-using-query-list-parameter/

[Anonymous]

Same problem. Seems like such a basic functionality. Dynamic queries using a list parameter. 

[Anonymous]

Same problem here. Need to pass parameters to a SQL query and had the same issue. I used the method described in this article: https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-b...

[Caroll]

Hi @akwAUBI,

I am currently trying to work through a very similary issue... were you ever able to find a solution to this? I noticed you posted your question several months ago.

Thank you

[tedtea]

Bump to keep this topic alive; has anyone managed to find a solution for this?

[blueant]

I met similar error.

In fact, I have a report and it works well. But today when I opened the query this morning, this error comes. I did not do any changes and after some hours this error disappears.  😞

[tedtea]

same issue - has anyone found a solution to this?

[Anonymous]

Hi @akwAUBI,

You can refer to below link to fix the issue:

Power Query Errors: Please Rebuild This Data Combination

Regards,

Xiaoxin Sheng

[akwAUBI]

Hey @Anonymous,

I had reviewed that article prior to posting this question, linking it and mentioning that it was not entirely clear to me. Reason being was that the step by step that Ken Puls provided in that blog post arguably painted a broad stroke across this error, for example one of the early code blocks where instructuion to alter syntax was just delete a many rows of text without full explanation why. 

A blatant difference between the blogged use case and mine is that Ken was performing a true dataset query merge whereas I'm trying to make a parameter reference values in a list query and sent the selected result to a separate query; but understand that all these queries fire off at the same time.

As mentioned, I'm interested in syntactically nesting these queries and their resultsets so as to satisfy the dependency chain behind the data I intend to report.