Forum Discussion
How to prevent refresh of data when using outer join ?
Hi all,
I need to know in a table if this is the first time the application is deployed.
Algorithm is as follow :
- get all applications (from csv file),
- for each application, get the application deployment informations (application details, deployment environnement, deployment date, version deployed, one HTTP call per application, 200+ applications....)
- determine if this is the first time the application is deployed with this version on the environment.
In order to determine if this is the first time, the solution I found is to sort all lines by application name, environment, version, and deployment date, and the compare current line with previous line to check if this is the same version, environment and application. If yes, insert in a new column the previous version, if no, insert "first".
The solution I found to compare from previous line is to user outer join, as described in this site (in French, sorry ;)) : Trucs et astuces - PowerQuery - Archétypes synergies (excel-formations.fr)
- sort lines as described previously,
- add to indexes,
- insert in a new column an outer join (= Table.NestedJoin(#"Index ajouté", {"Index"}, #"Index ajouté", {"Index.1"}, "Index ajouté", JoinKind.LeftOuter))
- then compare the values to determine if this the first time the version is deployed.
Problem is : result is very slow. I can see that for every line, when the compare is done from the outer join, a new HTTP call is done. I tried to use a buffer in different steps, but result is the same : the query is incredibly slow.
Questions are :
- would you have a better solution to compare the lines, and determine if the version is deployed for the first time?
- or would you have a solution to prevent a new HTTP call every time?
Thanks for your help 😉
2 Replies
- lbendlin
Super User
There is no need for nested joins. Do a Table.Buffer and then a Table.SelectRows.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Sorry about that.
Here is a sample data : I would like to add the column previous_version, based on the formula described before.
Before :
application_code deployment_date version environment AFK 12/01/2023 12:05 1.0.0 staging AFK 14/01/2023 14:27 1.0.1 staging AFK 14/01/2023 17:04 1.0.2 staging AFK 15/01/2023 09:37 1.0.2 staging AFK 16/01/2023 23:05 1.1.0 staging AFK 23/01/2023 04:34 1.1.0 staging AFK 24/01/2023 10:43 1.1.1 staging AFK 17/01/2023 03:56 1.0.2 prod AFK 18/01/2023 12:54 1.0.2 prod AFK 19/01/2023 17:43 1.1.1 prod After:
application_code deployment_date version environment previous_version AFK 12/01/2023 12:05 1.0.0 staging first AFK 14/01/2023 14:27 1.0.1 staging first AFK 14/01/2023 17:04 1.0.2 staging first AFK 15/01/2023 09:37 1.0.2 staging 1.0.2 AFK 16/01/2023 23:05 1.1.0 staging first AFK 23/01/2023 04:34 1.1.0 staging 1.1.0 AFK 24/01/2023 10:43 1.1.1 staging first AFK 17/01/2023 03:56 1.0.2 prod first AFK 18/01/2023 12:54 1.0.2 prod 1.0.2 AFK 19/01/2023 17:43 1.1.1 prod first I will try again with a Table.SelectRows, thanks.
