If I add custom column in Power Query that depend on specific column - each row the API get request?

[Mornagli]

I fetch my data with MPBI from Odata feed with an API call, the data is approximately 50,000 rows.

After i get tha data, when im in Power Query - I try to add new custom column that relies on existing column.

I want to know if every cell that I create in the new column - the MPBI send  another API call to our server to get the data...
This proccess cause a lot of problems and i try to understand why is this happening.

And is there a way to make those changes in Power Query locally with the data that i allready fetch instead with the extra API call?

[lbendlin]

Yes. show a sanitized version of your M code.

[Mornagli]

Thanks @lbendlin 

What im doing is:
- Go to Transform Data
- Put my API URL and get the full Metadata

- In specific table I -  'EXPAND' to specific sub-table
- Then i mark in the specific Table only the columns i need

And after this, when i click 'Close&Apply' - The MPBI stuck and it make problems to the server of my API.

Also if i add columns it make problems...

I try to understand how can i make changes, add columns in my data in MPBI without call the API for each cell....

As i see it, when im in power query, After i load the data from the Odata feed, every change is locally but maybe i dont understand the proccess...

[lbendlin]

Since this is an ODATA feed  - have you considered using ODATA filtering for your request?

[Mornagli]

Hi @lbendlin ,

I need couple of tables from this source, so i put the url that give me all the tables, And from it i take the tables i want.

If i will fetch each table separately, it will make diffrence?

With the filtering i will use for create 'Incremental refresh', but i need to understand why changes that i make in the Power Query after i have the table make problems when i try to 'Close&Apply'... And if there is an option to add columns and make changes in the Power Query' without call again to the API server for each cell.

[lbendlin]

if you already know which tables and columns you need then you can avoid a lot of work.  If however you don't have that prior knowledge then you have to use the long winded process.

[Mornagli]

Thank you for your replays @lbendlin .

I know which tables i need, but maybe in the future i will need more.
More importent for me is to know if after i choose specific table and load it to the Power Query, the changes that i want to do can be only loccally and not to active tha API GET request for each change. 

[lbendlin]

That's a decision you have to make for yourself - depending on size and source performance . Try one way and if it's not satisfactory then go the other route.

Also:  You need to distinguish between the effect this has on you as the developer/dataset refresher and the effect it has on your report consumers.  If you have to wait longer but they have a good experience then I would call that acceptable, definitely better than the other way round.

[Mornagli]

@lbendlin Sorry for the misunderstanding, But the effect that i want to prevent is to send for each cell that i add in a new column an API request to the Odata feed server - this is my main goal, So is there a way to add column that use existing column data, Without use my data source?

Why after i load the table to the Power Query, When i make changes, the MPBI does not use the data on the power query?

Thank you.

[lbendlin]

yes, you can reference any prior step in your Power Query.  What you cannot avoid is the effort to pull the data from the source. Somehow you have to pull that, and it doesn't matter much* if you pull it all in one call or in separate calls.

* It all depends on how performant your data source is.

[Mornagli]

@lbendlin Thank you again.

So if im in transfoirm data ->  enter my ODATA full metadata url and choose table -> in this table i 'EXPAND' another syb-table -> and choose from the table only specific columns that i need.

After all this proccess when i click 'Close&Apply' - the MPBI stuck for a long time and the DB server is cause problems.

Is that a problem that come from the performant of our DB API? 

I try to find a way to make the fetching easier to the API, If i will load instead of the full metadata, only the specific tables that i need, will it help?

thank you.

[lbendlin · Accepted solution]

yes, and yes.

[Mornagli]

@lbendlin Last question, My Data source does not have the Native Query eneblad, So
i created function to support folding that convert date to the MPBI format and it works,

Maybe when i click 'Expand' and then close and apply cause my server errors - the problem is that the Source not folding and then it fetch a lot of data that not necessary?

I saw more peoples in the forum that have the same errors and it seems that MPBI send to much HTTP request on each cell and that cause the problems.

I attached the link

Maybe there is a solution to those problems and i didnt found it.

[lbendlin]

the link you posted matches my experience. I think that often the data source thinks it will only handle the occasional request (like a microservice) and it is not optimized for handling lots of requests or spooling large amounts of data. You will have to experiment with a compromise setting (for example fetch data in batches of 2000 records).