PowerBI Service Dataflow & Datamart & PowerBI Desktop

Hi zhanna27 

I am glad you find my tips helpful. 

Now, to your new issue. Not sure if I understand it correctly - I think what you are saying is that the dynamic data you receive in .csv file contains only data for the current day. But what happens to the previous files from yesterday, the day before, etc? Is the file replaced with a new file every day, so you don't keep the previous days in separate files? If you don't keep the files from the previous days, then you can't really achieve this in PowerBI as whenever you need to refresh the Dataflow, it will only use the files you currently have. e.g. can't get data which is no longer in the datasource. So, in this case, first, you need to sort out the files from previous days to be kept in the folder, and you only add a new file to the folder every day. This way, you keep all the 'dynamic' data files in a folder, so when the Dataflow is refreshed, it can read data from all files within the folder. Depending on how you get the new file every day, there are different ways to sort it out. In my case, I receive the new file via email everyday, then I have a simple PowerAutomate which gets the attachment from the email and saves it in a SharePoint folder for me (if the file name is the same everyday you can add a date at the end of the file name when saving to the folder).  This way, the process doesn't involve any manual handling of the data as it is automatically done for me even if I am away. Once the file is uploaded then the scheduled Dataflow is refreshed and the report updated with the latest data. So, the key is that you need to keep all the files for the 'dynamic' data in a single folder, then every month or couple of months, you can move the files to the 'archived' data folder and then remember to refresh the archive data Dataflow to get it updated and the 'dynamic' data folder is empty again so your daily refreshed Dataflow refreshes nice and quick. 

One thing you mentioned is that you have the data on your desktop? Although it may work, I would strongly recommend moving the data into SharePoint (I hope you can create your own site within your company or can be given access to any existing SharePoint). You would need to have two folders - one for the archived data files and one for the dynamic data files, so the Dataflows will load all files from these folders. Which brings me to the next point, how do you load the data from files? Is it just load from Excel or do you use load from folder (SharePoint Folder). The best method in your case is to use Load from SharePoint Folder where you need to provide link to the main SharePoint site then filter to the folder you want to load and when you see all files within the folder, the easiest is to create a simple function to extract data from each file and then combine into one single table. Also, I always use formula =Excel.Workbook([content]) in a new column to get a list of all elements within the workbook, then you can filter to the item which you want to extract e.g. Sheet1 or Table_name to only work with what you need and then apply a function to extract/transform the data for me before expanding the final output into a single table.