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.
You can see in the file attached a solution to keep existing data in Power Query and append data from same file as new rows at each refresh.
Each time you press refresh, same file data will be appended again and again. (change source file path in Source file sheet)
Thanks Catalin,
but your solution is not what I need.
I have to be able to compare two lists "n" times ( t1,t2,t3....tn) and count the records that come and go in the two periods and then stratify over time.
Example in the attached files (three excel files)
T1= 8 item "giacenti"
T2= 16 item "giacenti" of wich 8 items news )
T3 =23 item "giacenti" of wich 9 item news + 2 item defined (definito)
Stratification
- Stratification new = 17 item
- Stratification definito= 2 item
and so on for t4 t5, tn......
Ciao
Stefano
The last 3 files brings even more confusion.
Initially, you had:
| Date | Code | Name |
| 4/02/2020 | 35 | Lara |
| 5/02/2020 | 36 | John |
| 6/02/2020 | 37 | Steve |
| 7/02/2020 | 38 | Lara |
| 8/02/2020 | 39 | John |
| 9/02/2020 | 40 | Steve |
Now, you have:
| 1/01/2020 | John |
| 2/01/2020 | Paul |
| 3/01/2020 | Charles |
| 4/01/2020 | Mia |
| 5/01/2020 | Sia |
| 6/01/2020 | qui |
| 7/01/2020 | quo |
| 8/01/2020 | qua |
What defines the uniqueness of a record? The name or the Code? I thought it's the code, based on your initial data.
Anyway, see attached way to solve some of the questions with power query and Power pivot, hope this point you in the right direction.
If your way of doing things is based on importing data from a folder, then you should change the data query, now it takes data from the same file as described in previous message.
If your unique records are identified by name not by code, you have to change the measures accordingly.
