• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / How to manage flow over the time|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to manage flow over the time
sp_PrintTopic sp_TopicIcon
How to manage flow over the time
Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
1
February 3, 2020 - 2:38 am
sp_Permalink sp_Print

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 KissKissKiss

Stefano

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
February 3, 2020 - 2:55 am
sp_Permalink sp_Print

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?

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
3
February 3, 2020 - 3:09 am
sp_Permalink sp_Print

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

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
4
February 3, 2020 - 3:11 am
sp_Permalink sp_Print

sorry...."first" is a workbookEmbarassed

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
5
February 3, 2020 - 4:38 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
February 4, 2020 - 3:23 pm
sp_Permalink sp_Print

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.

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
7
February 5, 2020 - 6:49 am
sp_Permalink sp_Print

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

ThanksSmile

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
February 5, 2020 - 12:23 pm
sp_Permalink sp_Print

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?

sp_AnswersTopicAnswer
Answers Post
Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
9
February 5, 2020 - 3:20 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
February 5, 2020 - 4:11 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
11
February 5, 2020 - 6:17 pm
sp_Permalink sp_Print

Sorry  , for the wrong reply....

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
February 5, 2020 - 6:44 pm
sp_Permalink sp_Print

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?

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
13
February 5, 2020 - 6:51 pm
sp_Permalink sp_Print

Yes.

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
14
February 6, 2020 - 12:35 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
February 6, 2020 - 2:52 am
sp_Permalink sp_Print

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?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
16
February 6, 2020 - 3:09 am
sp_Permalink sp_Print sp_EditHistory

customers.jpgImage Enlarger

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.

sp_PlupAttachments Attachments
  • sp_PlupImage customers.jpg (41 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
17
February 6, 2020 - 3:33 am
sp_Permalink sp_Print sp_EditHistory

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)

Avatar
STEFANO ROSSI
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 22, 2019
sp_UserOfflineSmall Offline
18
February 6, 2020 - 7:11 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
19
February 6, 2020 - 4:36 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Aminu Sule
Samuel Ramirez
Abdifatah Osman
mohad borhom
Abayomi Adedeji
Ganesh MVS
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6364
Posts: 27828

 

Member Stats:
Guest Posters: 49
Members: 32346
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.