Cannot expand View in Power BI Power Query

[BladeRunnerRI]

I am using a Power Bi .pbix file produced by Microsoft to assess data produced by the Microsoft Readiness Toolkit for Office.  They have several visuals that cannot load due to the size of the file the returned data exceeding the Power Bi maximum (7.5M rows).  In order to resolve this I created a view on the SQL server backend which selects only specific data.  When I add this view to the Power BI  model and setup the relationships as the previous tables and try to expand the columns in the view query,  I get an error:

Original Table Query with expanded columns (which works) is as follows :

New View Query with same expanded columns (which does not work) is as follows :

As I've stated the Many to 1 relationships with the other tables in the model have been setup in the same way.  Any thoughts will be most welcome.

[ToddChitt · Accepted solution]

What is the definition of your view? Is it simply SELECT * FROM <some single table> WHERE <you want to limit the data> ?

If so, you can do one of two things:

1) Use the original M script, but where it states Item = "VbaDocuments", simply substitute in your view name. Note that the view must have all the columns as the original table.

2) Assuming this is a SQL Server table, put in a filter condtion as the third step (before Expand). Check if your query is folded into SQL Server. Do this by right-clicking on the Applied Steps and selecting View Native Query. See if it wrote in a WHERE Clause for you.

[v-jingzhan-msft · Accepted solution]

Hi @BladeRunnerRI 

This is probably because that the first expand step didn't reference the correct previous step by name. From your screenshot, it should be dbo_vw_VbaDocuments instead of Readiness_VbaDocuments.

In addition, if the expanded column is brought by the relationship, you need to ensure they still exist when you connecting to this view. You can remove the last two expand column steps, then try to expand the columns manually if they are just there. This won't bring the error. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[ToddChitt · Accepted solution]

What is the definition of your view? Is it simply SELECT * FROM <some single table> WHERE <you want to limit the data> ?

If so, you can do one of two things:

1) Use the original M script, but where it states Item = "VbaDocuments", simply substitute in your view name. Note that the view must have all the columns as the original table.

2) Assuming this is a SQL Server table, put in a filter condtion as the third step (before Expand). Check if your query is folded into SQL Server. Do this by right-clicking on the Applied Steps and selecting View Native Query. See if it wrote in a WHERE Clause for you.