Continuously updating and appending of data

[CARL12345]

Hello together,

I would like to create a dashboard about my production capacities. Our ERP system reschedules daily the orders on the capacities. The capacity picture for fridays as an example can look very different on Monday of the week than on Tuesday.
In order to show the development of the capacity utilization, it would be good to see how the capacity utilization for Friday was on Monday, Tuesday, Wednesday, and so on. Unfortunately, I can't find a table in the ERP system where the old data is stored. I can only pull the current planning from the system in the form of a .csv file. What is the best way to ensure that the planning data is continued day by day?

My idea in Excel is to read the planning from Monday into Power Query and then output it to an Excel table. On Tuesday I read the new .csv file as well as the Excel table back into PowerQuery and paste it to the table in Excel etc..... This would work for Excel. But since at some point the rows in excel are also used up, I want to do the whole thing in PowerBI. How would I be able to do this? I hope you can help me.

Thanks in advance.

[jdbuchanan71 · Accepted solution]

@CARL12345 

It sounds like your best bet would be to have export the .csv each day and import them all into PowerBI.  If the file name contains the date it was exported, you can use that to see what the data looked like on a given day.  Take a look at this video about imorting files to PowerBI.

https://www.youtube.com/watch?v=UY3hIV-THcg&ab_channel=Curbal

[CARL12345]

Thank you both for your fast reply. Saving with different Date is Not a really good Option. I will have a Look to Dataverse to find Out If i can Use it and If so for what i can Use it. For the First steps I think i will try it with Storing Data in an Excel Sheet. 

[JoeBarry · Accepted solution]

As @jdbuchanan71  mentioned, you will need to create a date in the name or there is already an export date in the CSV and export each day.

It isn't very sustainable to import multiple CSVs in to Power BI Desktop. It will slow down the refresh and if there are any changes to column names or a column is removed, then this will break the report. 

Do you have the possibility to import this data daily into a Database from your company? Maybe Dataverse? You could also use Dataflows or Datamarts, if your company has Power BI Enterprise Premium you could append all tables in a Dataflow. 

If you decide to go down the Power Bi tools route, only export the columns you actually need if possible. 

Thanks

Joe

[CARL12345]

Hello Joe, i looked a little Bit about Dateverse and I think I got found the Option to load the Data to an existing Table. (See Screenshot). I also think that i can Use Dataverse for a Lot of "my problems" for creating dashboards. I don't have direct access to our Database with Power BI and for each Data i need i have to write a SQL query First, save the Data AS .CSV and the importing IT to Power BI. Because i would Like to learn more about Dataverse and the possibilities: do you have a good Webpage, Tutorial, book....for me where i can learn more about this topic. Microsoft explanations are a little bit to IT intensive for me. 😞

[JoeBarry]

Hi Carl

To use dataverse, you will need a Power Apps license. 

Here is a link to give you a good overview. https://learn.microsoft.com/de-de/power-apps/maker/data-platform/data-platform-intro 

Here is a free course from Microsoft https://learn.microsoft.com/de-de/training/paths/get-started-cds/

If you have the correct permissions, you won't need to use an existing Table, you can create your own. To load the data on a daily basis, you can create a Dataflow to do this. When this is all set up, all that you need to do is to add the CSV to the folder that dataflow is taking the data from. 

Before diving in, do some testing and then launch. 

Beste grüße

Joe

[jdbuchanan71 · Accepted solution]

@CARL12345 

It sounds like your best bet would be to have export the .csv each day and import them all into PowerBI.  If the file name contains the date it was exported, you can use that to see what the data looked like on a given day.  Take a look at this video about imorting files to PowerBI.

https://www.youtube.com/watch?v=UY3hIV-THcg&ab_channel=Curbal