DataVerse Direct Query with field selector

[ATREZISE]

Hi Everyone

I'm tring to connect to our dataverse environment using direct querty but only want to pull a limited number of fields in the hope that this will improve performance.

I've done this before using ODATA and the $select command

Is there a similar method using dataverse?

Thanks in advance.

[ATREZISE]

Thanks for your reply

Doesn't selecting columns after the data load mean that we have already fetched all the column data? i.e. it is to late by then...that's why I thought we could just select the fields we required as part of the query.

[selimovd]

Hey @ATREZISE ,

depends on the query.

In general if you use a relational database as a source and you do a few easy steps, these are already transformed into a relational query.

This is called query folding in Power Query:

Query folding | Microsoft Docs

So if you need only 2 specific columns, Power Query doesn't load 10 columns and will then delete again 8 columns. Instead it will be translated into a SELECT  statement and only these 2 columns will be selected. 

You can always see the native query by clicking with the right mouse button on a step and chose "View native query":

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[ATREZISE]

ah...so are you saying that only the final SELECT query is executed?

The Navigation step looks like this...

However the next step (which removes the columns) only has the required columns...interesting though that it is selecting from a different source.

[v-robertq-msft · Accepted solution]

Hi, @ATREZISE 

According to my test, the SELECT query selected the columns from the same data source.

This is the original query:

This is the query after “Remove column”:

Although the query is a little bit different, but the table name after the “from” is the same.

Therefore, I think you can create a table with fewer columns to connect with Power BI and check if you can get the same result as mine.

Thank you very much!

More info about the “View native query”

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @ATREZISE 

According to your description, you want to pull a limited number of fields into the Power BI when using the direct query, I think you can use the Power Query editor to achieve this. You can click on the “Transform data” when you connect to the data source and select the tables you want:

Then you can press on “Ctrl” to click the columns you want to remove, then select “Remove columns”:

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.