How to manage flow over the time|Power Query|Excel Forum|My Online Training Hub

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 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.

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?

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, 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?

STEFANO ROSSI said
I think that PQ is a good solution...  

Sorry to disappoint you, what you want looks more like a customer count over time normally used for e-commerce, see image attached. This is handled by Power Pivot, not Power Query, PQ will just bring the data, the rest of time intelligence calculations are handled much better in PP.

Instead of using the same First.xlsx file every day, you can use a template and save it everyday with the date as file name, you can easily combine them in Power Query.

It is possible to keep existing data while getting new data, but it's just a workaround and not a solid solution.