Day over Day - Freezing data?

[cmcreynolds]

Okay - so the inevitable has happened: I've been asked to compare data day over day ("progress"). I at least managed to get my bosses to understand they want "last full day" compared to the "previous full day" instead of today versus yesterday.

I am connecting to Salesforce (in case anyone cares) and those records change day to day - one record can go from "inquiry" to "prospect" - is there a time field? YES! BUT, some are manually entered. (Example, Sarah gets an e-mail on Wednesday saying the application was received last Friday, so the field is populated with Friday's date) Therefore, the date fields aren't really reliable.

So, I was thinking the best solution would be to download the data, freeze it and do the same the following day - then, I'd compare the two. BUT, how can I do this as an ongoing process? Is there a way to "discard" older date freezes? (My boss just wants yesterday compared to the day before - again, "progress")

I don't need code right now, just some conceptual ideas. Maybe I don't use PQ or PP? Just Excel?

Added super bonus - I need to exclude weekends. (So, on Tuesday, I would be looking at data from Monday and Friday).

I think this is going to incorporate VBA or Macros, which I'm very unfamiliar with, so be gentle, kind MrExcel-ers!

Thank you!

 

[cmcreynolds]

You only need the function approach if the file needs to be reshaped. If you files are already well shaped ( in tables like you need in power bi, you can use the other method.

Okay, I'm getting lost in a language loop here. Here's what I know:

1. I know how to combine CSV or EXCEL files with PQ
2. I know how to create a daily pull of data that includes a timestamp.
3. I know how to shape my data into the way I need it.

What I'm not understanding how to do, and what I do not see on the blog links you are providing, is how to automatically produce the daily pull. I can do it manually and that involves me saving with different/structured file names.

 

[Matt Allington]

If you are using one of the file combine techniques, the file name is irrelevant. The techniques in the links work on all files in a folder regardless of the file names. Can you explain what the issue is.

 

[ImkeF]

To me this looks as if cmcreynolds is talking about how to get the data into the storage, whereas Matt is pointing to solutions on how to get them from the storage into the reports.

@cmcreynolds: If you decide not to take one of the tried & tested standard-methods that Matt has wisely and elaborately explained, you should be aware that you might travel a lonely path where help might be harder to find.

But if you want to go for a simple-stupid-method (simply store your data from the 2 latest days), and your daily data boils down to just one table with no more than 20 k rows, then I see no reason at all why this cannot be accomplished in a single Excel file: Set up calendar that only contains the days that shall be taken into account for the reports. Create a column where 1 and 2 alternate. Then create your load procedure (either in Power Query or with the help of VBA) that checks the current date and retrieves the number from the table where the current load go into.

If you want to achieve a behaviour like this in Power Query without VBA, you have to create 2 queries: First filling table1 and the second one filling table 2. They need to contain a condition where they drag their source from: If the current day is their number, then the source is salesforce, otherwise it’s their latest output (as an Excel-table, so no circular dependencies will occur).

That way one query will always fill one table with the current salesforce-data and the other will show it’s own values (which have been refreshed the day before).

 

[cmcreynolds]

Thanks, Imke

No, I'm not wanting just the last two days. My confusion exactly lies in getting the data into storage. I am pretty sure I know how to get the data into the reports, I'm just confused on how to prepare the data.

 

[ImkeF]

Also having difficulties understanding your requirement.

How about this then?: Please read this method:
How to create a Load History or Load Log in Power Query or Power BI – The BIccountant

and let me know how how this differs from what you want/need.

 

[cmcreynolds]

Imke - the description in that link is exactly what I intend to do. I have only glanced at it as I'm on another project momentarily, but I will look at it closer tomorrow.

Thank you! x999,999

 

[cmcreynolds]

Just as a heads up - the bosses changed their minds on looking at "frozen" data. Whew! Onward!