Evolution of the data in a project where the data is overwriten at every refresh

[Azounet]

Hello,

Introduction:

I am a beginner with a pro license who wishes to create his first PowerBI project. I have been watching full courses on YT and doing some of the Microsoft learn modules aswell. In this post I will describe what I’d like to create, and what are the steps I will have to complete in order to do so, from what I have understand.

I'd like to know if I have got all the steps right, or if you have any warnings, or tips on how to better complete a step. I also have a few questions along the way.

Project introduction:

We have multiple workshops in different regions. They have an old software that tells them what maintenance tasks needs to be done on what equipment. Every task is a line in the software. For now, I'd like to build a simple report with the number of lines for each type of task. With the segments, I will be able to see for each workshop or region how many lines are late, and how many hours of work that represent.

A second report far more interesting will be the evolution of the numbers of the first report.

Let's say I update the data every Monday. I want to know for every region and workshop if we are improving or not. After a few weeks, we can have a graph going down to our objective. If I have 100 tasks late this summer, we can have an objective to reduce it before 2024.

I want the workshop managers to be able to access the reports and extract the data. Being able to watch the data in PowerBi will certainly make them want to improve their stats and help them clean their database.

If I'm not mistaken, this will require an app. Although it will only have 2 reports for now, having the app shared with all the managers will be useful for the future projects.

Useful info:

I cannot connect directly to the database, but I am able to extract all the data in Excel easily. Furthermore, I can save the shape of the data so after doing it once so only 1 click is required to get a clean new Excel sheet with the latest data.

Datasource: Excel extracted from the software, and 1 (or2) other Excel sheets for the tables

Tables (so far):

1/ Ongoing tasks (Contains all the lines of work, with the description, the hours it would take, worktype, workshop code, equipement)

2/ Region/workshop (Contains 2 columns with the workshop code and the region associated)

3? / Evolution (Contains the date of refresh and numbers of lines for each type of work, and any other stat I’d want to see the evolution of) I am not sure I need that 3rd table.

  [1*:*2] There’s a “many to many” relationship between the tables 1 and 2.

Questions:

How should I manage the refresh?

As said, my source is an Excel sheet extracted from the software we use. For every refresh I will have a new Excel. How can I manage that? Do I open the n-1 Excel and replace all the data by the new one? Is it ok/mandatory to have it on a OneDrive / Shared OneDrive?

How should I track the evolution of the data?

I need to know if the workshops are improving or not every week. But the information is the number of lines (tasks) and the sum of the “estimated hours of work” in my dataset. I need to keep track of these information.

My excellent idea was to have an Excel with macro as my datasource:

Whenever I’d need a refresh, I would

1: Get my extraction from the software

2: Paste it in the first sheet of the Excel datasource

3: Activate a macro to create a line on a Sheet “Evolution” (which serves as a table) and copy all the info calculated on another sheet (which is NOT a table on PowerBi) to add it to the line previously created.

4: Then save and Refresh in the PowerBi Service.

I started doing that but eventually realized I had to track every number for every workshop, which is fine but… there must be a better way than having 50 columns for every worktype or every workshop. Also, by doing so I won’t be able to use “analyses” on the graphs…

I will restart from scratch, as my attempt was more of a test to first use PowerBi, build reports and so on.

Step by step:

Extract my data>Get it on an Excel Online > Add the other tables> Get the data with PowerBi Desktop> Build my reports>Publish the reports a workspace previously created in the service> Create an app>manage roles and permissions> Share it.

Thank you so much for your time, let me know if you need any precision,

Azounet,

PS: Didn’t include any data, I don’t think it is necessary, but I could make a sample if needed.

[Azounet]

Hi,

Everything worked as I wanted. It takes me about 1min30 to refresh the data, juste need to copy paste datas from the extractions into the main Excel then run the macro that update the Evolution Sheet.

The size of my dataset won't increase a lot, only about 50lines each refresh and all the data is used in my reports.

 So yea I'm pretty happy, I've improved a lot in august 🙂

Thanks again,

Az

[Chris_White · Accepted solution]

Do your 'evolution' files contain a 'date of snapshot' column?  You've not mentioned this and it's what you'll need if you want to track changes over time.  You can macro this in if it's needed.

Power BI can load all files from a folder, so you can drop your evolution-files into a folder and have Power BI collate them for you.  This folder will quickly grow to contain lots of files, but this should be fine.

[Azounet]

Hello,

Thank you for your answer!

I though that wouldn't work for long because if I have around 10 000 lines, each refresh would add 10 000 lines to the dataset and i was a bit scared it would damage performance.

What I am going to try this afternoon is to keep only the numbers that interest me in the "Evolution" sheet, and replace every old data with the fresh one. 

I kept a backup for the old data so if i ever decide i want more insights than what i have with my solution, then i will implement yours.

I will keep the post updated  if it works well 😄

(Also on how long it takes me to refresh)

Az