Right now, I'm tracking property, and I do so using a script to download IT reports (produced weekly) and using PowerQuery to import these different data sets into one workbook. This allows me to enter the UID for an asset, or many assets, and get a report on all accessible data for the entries.
What I WANT to do is develop a new form structure that will only be useful for one asset, but dig into its' history by accessing multiple files. I have almost no experience with scripting, but I don't think this is something I can do with Power Query alone.
My file structure (for one of these four files) looks like this: (Yes, I know Y/M/D is weird, but it sorts chronologically in an alphabetical search, and that keeps my archive tidy.)
20230629 - Active Assets
20230622 - Active Assets
20230615 - Active Assets
The key that I will use to find the line with information I need is in the form of a serial code; sometimes entirely numerical, sometimes with some letters, but it should always be in a specific, expected column, and it will always be unique. For instance, the "Asset Tag" will always be in column B in the Active Asset files I used to demonstrate my naming convention.
And so on. As an FYI, the exact date and filenames are not predictable (not every 7 days exactly), so I can't rely on predicting the dates that will be used for the filenames.
I want to be able to enter the UID for an IT asset, and have it pull up what these files report for the four most recent files, and the first file that's at least 3, 6, 12, 18, and 24 months old.
As said, I have almost no experience with macros and scripting, and every time I try to use them it seems like a round of torture to make it work, even when they're pre-written by someone who knows better. But I'm also pretty sure that there isn't a way to get files in such a dynamic way via Power Query's stock features.
If anybody has an idea of how to solve this or a good idea where I should begin learning, I'd appreciate it. This feels like a much heavier lift than I could develop via scripting as a beginner.
What I WANT to do is develop a new form structure that will only be useful for one asset, but dig into its' history by accessing multiple files. I have almost no experience with scripting, but I don't think this is something I can do with Power Query alone.
My file structure (for one of these four files) looks like this: (Yes, I know Y/M/D is weird, but it sorts chronologically in an alphabetical search, and that keeps my archive tidy.)
20230629 - Active Assets
20230622 - Active Assets
20230615 - Active Assets
The key that I will use to find the line with information I need is in the form of a serial code; sometimes entirely numerical, sometimes with some letters, but it should always be in a specific, expected column, and it will always be unique. For instance, the "Asset Tag" will always be in column B in the Active Asset files I used to demonstrate my naming convention.
And so on. As an FYI, the exact date and filenames are not predictable (not every 7 days exactly), so I can't rely on predicting the dates that will be used for the filenames.
I want to be able to enter the UID for an IT asset, and have it pull up what these files report for the four most recent files, and the first file that's at least 3, 6, 12, 18, and 24 months old.
As said, I have almost no experience with macros and scripting, and every time I try to use them it seems like a round of torture to make it work, even when they're pre-written by someone who knows better. But I'm also pretty sure that there isn't a way to get files in such a dynamic way via Power Query's stock features.
If anybody has an idea of how to solve this or a good idea where I should begin learning, I'd appreciate it. This feels like a much heavier lift than I could develop via scripting as a beginner.
