Log In
Register
Home
April 22, 2019
Offline
Hi all,
I would be gratefuk to everyone who will help me solve two problems
1)
I tried to manage flow over time with PQ but something does not work....sob 😉
Attached you will find two files....containing source and data
2) (and more difficult without VBA)
Automate all the steps (copy first,download second and finally stratification )
Sure I will find here a good solution
thanks in advance 
Stefano
See Answer
November 8, 2013
Offline
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?
April 22, 2019
Offline
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
April 22, 2019
Offline
sorry...."first" is a workbook
April 22, 2019
Offline
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 ???
November 8, 2013
Offline
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.
April 22, 2019
Offline
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
November 8, 2013
Offline
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?
Answers Post
April 22, 2019
Offline
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....
November 8, 2013
Offline
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.
April 22, 2019
Offline
Sorry , for the wrong reply....
the right replay is: they disappear because they have been processed...and they cannot come back!.
November 8, 2013
Offline
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?
April 22, 2019
Offline
Yes.
April 22, 2019
Offline
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...
November 8, 2013
Offline
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?
November 8, 2013
Offline
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.
November 8, 2013
Offline
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)
April 22, 2019
Offline
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
November 8, 2013
Offline
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.
1 Guest(s)
