Creating a query as a view of another query

[Anonymous]

Creating a query as a view of another query

Hi,
I need to import a SQL customer table creating a query inside Power Query. In particular, this table has a state code.
Next, I need to perform some data transformations.
Then, I need to split data in two different queries respect to the state code value, f.e. UK_Customers and Other_Customers,
in order to accomplish other specific data transformations.
I'd like to creating two different queries having a behaviour similar to a SQL view, without reloading UK customer data
in the UK_Customers query and Other customer data in the Other_Customers query from the All_Customers query.
Is it possibile to implement a such query?
Any suggests?
Could a such question be a future idea for Power Query?

Many thanks

[Anonymous · Accepted solution]

Hi Jimmy,

do you say to create a basic query, to accomplish an initial data loading about this query, to perform the basic data transformations, to create the necessary derived queries with the related specific data transformations and then to disable the include in report refresh option for the basic query while the data loading for the derived queries is to execute?

[mahoneypat]

Would it meet your need to make two reference queries from your initial query and then turning off parallel loading of tables?  That should result in a single pull from your database.

Pat

[Jimmy801]

Hello @Anonymous 

I don't think so this would work. The thing about power query is that even you have a basic table, that you are NOT loading in to your datamodel, but using as a basic for lets say two other queries, this basic query will run exactly 2 times. Because the result is not possible to buffer somewhere. Also in dataset point of view to load all customers and UK-customer twice into a datamodel would not make to much sense. I'm not a Expert on the datamodel itself.. maybe there are such possibilities like views, but as I mentioned in Power Query this is not possible

BR

Jimmy

[Anonymous]

Hi Jimmy, thanks for your reply.

It could have a sense to have a first query containing all working data and some data transformation steps and one or more derivated queries with no data but having some specific data transformation steps, in order to separate the data transformation logics. Inside Power Query, a query contains data and becomes a table in the relates semantic pbi model. In a database management system, a table contains data and a derivated view is a query definition to retrieve specific data from the table and not contains data; it is possible to have a table with data and more related views without data, so the data are loaded only once time.

[Jimmy801]

Hello @Anonymous 

I know you what you mean, but thats not possible with Power BI. Or at least I'm not aware of anything. Maybe other superuser like @Greg_Deckler can help here out.

What  you can do is to create a new table in DAX where you are creating a new view.. .to filter a column ... but in this case the data will be twice in your dataset. But I ask myself also why this is needed? When I think of SQL a view is helping to query the server in a more efficient way, but why should I need to query my datamodel within Power BI. Maybe I'm missing something here.

About your first point: This is for sure a good practise to create a basic query where you do basic transformation and then split this up to 2 other queries and not loading your basic table in the datamodel at all. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous · Accepted solution]

Hi Jimmy,

do you say to create a basic query, to accomplish an initial data loading about this query, to perform the basic data transformations, to create the necessary derived queries with the related specific data transformations and then to disable the include in report refresh option for the basic query while the data loading for the derived queries is to execute?

[Jimmy801 · Accepted solution]

Hello @Anonymous 

exactly. To make your queries more modular and so you have to change to code only once, even you are creating 2 tables of the same data. The downside here however could be that the firewall is hit, and if this is so, you would again need to include your basic query into the other one.

Hope I was clear 🙂

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy