Hi Stefano,

Can you describe the process? What steps needs to be made in your files to achieve what you're after and what does not work?

Hi Catalin,

I would like to update the worksheet "first" that manages records "that come out" (defined)," that enter (news)" and that remain (lying). The data changes every day and I would like to automate the update of "first".

Thanks a lot

sorry...."first" is a workbook

I try to explain myself better:

if I add a row in the workbook called "first"

the  query "Giacenti" in “applied steps” is correct with 80 rows

but the "Nuove" query, the "Nuove P" and "STRAT Nuove" query are all wrong ... why ???

Hi Stefano,

In Nuove query you have this:

= Table.NestedJoin(#"Giacenti Precedenti", {"Code"}, Giacenti, {"Code"}, "Giacenti", JoinKind.RightAnti)

The 2 queries used in this Join are exactly the same, therefore will return an empty table because Right Anti means "return all rows that can be found only in second table"

Nuove P is exactly the same query as #"Giacenti Precedenti" and Giacenti query, so it works the same, no idea why you are using the same query 3 times. All work the same and return the same data.

In STRAT Nuove, there is another strange thing:

let
Origine = Table.Combine({#"Nuove P", #"Nuove P"}),
#"Rimossi duplicati" = Table.Distinct(Origine, {"Date"})
in
#"Rimossi duplicati"

Basically, you intentionally duplicate the data by combining the same query twice, then you remove duplicates. Still works, but it's a nonsense from my point of view.

Make sure the First.xlsx workbook is closed when you refresh the queries, otherwise your code should be modified to handle open workbooks.

Hi Catalin,

I would like to manage records that come out (definito), that come in (nuove) and that stay in a table (giacenti).
To do this over time I have to rely on a second table where to copy the data before they change and then update it.
Whenever the data changes, it may be that someone leaves, someone enters and someone remains ... hence the idea of the Definito queries (for those that come out) and the Nuove query (for those that enter) ....the two query Definito Precedente e Nuove P  have the function of supporting to compare the data in two different periods.

Do you have a method to suggest me with POWER QUERY to manage these situations???

Thanks

I don't understand your process to be able to help.

Your first table is just a list of names, codes and dates.

How do you define records that come out? They just "dissapear" from the list in the next day?

Catalin Bombea said
I don't understand your process to be able to help.

Your first table is just a list of names, codes and dates.

How do you define records that come out? They just "dissapear" from the list in the next day?  

No.there are three different situations:they "dissapear" (definito), they enters for the first time ( nuove ) and they are different than definito and the last situation, records that not disappear and not news...stuck tecords....now in a little bit more clear....

I understand that there are 3 situations. My question was very specific, for one situation, to understand how that situation is handled, they just "dissapear" from next day list?

Your answer is "No."

Please clarify HOW they dissapear.

Sorry  , for the wrong reply....

the right replay is: they disappear because they have been processed...and they cannot come back!.

Sorry, still wrong answer.

I understand that they dissapear. The question is not WHY they dissapear, the question is HOW? That row is simply ERASED from the list?

Yes.

STEFANO ROSSI said
Yes.  

When the row is erased =definito

When the row is new= nuove

When the row dont move = giacenti 

And i want to manage a list where every day data could change!!!

I think that PQ is a good solution...

Ok, great, we made 1 baby step forward.

Question 2: do you want to see only current day compared to previous file? Or you want to store all previous days and display a variation over time?