Power query pivot table compare.

[citogrid]

Hi everyone, must be missing something... can't figure this one out:

I have item list of about 20000 products. (id, size, number,...)
No useful date field in this table.
My boss would like an overview of the changes in stock over time.
I can't show him the "past" because dates in list aren't useful (incomplete, missing, erroneous,..) but i'd like to do this from today onward.
What I would want to do with the stocklist:

Generate a pivot table with power query (I can do that)
Generate a pivot chart from that ( no problem)

Each time (daily) the stocklist is generated, I would want a datevalue to be added to the table in some way, so that the pivot chart is updated with new info.

So on monday I have a automatically genereated bar chart with one bar of info

On tuesday: 2 bars of info
and so on...



Any ideas on how to achieve this?

 

[SullyWYO]

How do you access the daily data? What is the Source for the Power Query (File, Folder, Database, etc)?

The goal would be to have each day's data stored in a separate csv spreadsheet using the date as a title. Then you can use the title as a date that can be added to each line of data.

In the case below, I just drop the .csv from the "Name" column and reformat as a date.

 

[citogrid]

Hi,
Thanks for your reply.
How do you access the daily data? What is the Source for the Power Query (File, Folder, Database, etc)?
Excel file generated by database upon export.

I can indeed, as you show, save the file with a date name (still need to figure out how to have that generated automatically) and then do some transformations in PowerQuery (remove file extension, convert to date format..) and then save the files in a folder.
Then generate query by importing from folder and load to pivot table.

However, what worries me is the size of the data load after some time... if I have a excel file with 20000 rows, 10 columns and add another 20000 rows every day, it's gonna get unworkable soon. So, (excuse the newbie question) how do I do a compare between 01-01-2022 and 02-01-2022 in powerquery so that only the 100 or so changed rows from 02-01-2022 are added to the file?

thanks again,
Ben

 

[SullyWYO]

You are correct - this quickly becomes an issue.

My solution is to use a "compiler" each day to review the data and save the results in a new folder. In your case, this would be the power query that you are using above. Each file is saved as "[date] - Compiled".

Then I use a separate Power query to compare the compiled daily data. In your case, it looks like maybe the compiled files would only be around 100 rows each, so you will be able to cruise for years.

I'm sure there is a better method that actual data analysts use for this type of thing, but this works for me.