Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Joao Ramos]

Hello everyone….

I have the following situation, I created a billing follow-up report, where every day updated with the information from the previous day, where a single file is generated with the information of just 1 day.

I did all the configuration of the RangeStart and RangeEnd, filter and increment of the table, however when I point to this file that contains only 1 day, I lose all the previous information, there is no increment but replacement of the data.

For BI to work, I’m having to increment (manually) the original file, with all the data.

Could someone help me in this case? If it is possible to do this automatic increment, where a new file is generated daily.

Thankful.

——————————
Joao Ramos
——————————

[Ben Howard]

Hi Joao,
If you are getting a new file each day for just that day, then you need to change the source dataset from a file to a folder.   PowerBI will iterate through all of the files (and folders) in the source folder, and append all of the files together.  They you build your report from that new query.  All you have to do to refresh the report is then just drop in the new file each day into the folder, and hit refresh.  If you store the folder in Onedrive for buinsess, then the Power BI service can automatically refresh this data, so you don’t even need to open the desktop tool.

——————————
Ben Howard
United Kingdom
——————————
——————————————-

[John Thomas]

is spot on.  This is what Power Query does really well.  If you can post three or four of your Daily files Ben (or I) would be happy to provide an example of how to do this.

?

?

——————————
John Thomas
NC United States
——————————
——————————————-

[Joao Ramos]

Hi ??? and ? thanks for your help.

My project is to calculate the production OEE Indicator, for which I need to collect information from 2 different locations / files:

Downtime Injector (.txt file) – Injector monitoring system, where I collect all types of stop and restart events, production cycle, among others:
I use the StartTime field as a reference for incremental update.

Product Quality (.xlsx file) – Generated by the ERP, where there is information on the quantity produced by injection and how much is approved and disapproved:
I use the Data field as a reference for incremental update.

These are the files that are updated daily and the Power BI should increase, keeping the history.

The files are already generated in the correct location, with the names that are registered in Power BI, where it must consume and update its database.

I have also already configured the Power BI Gateway, where the update is done at the scheduled times.

My only problem is this daily incremental update, where I have to add the one produced the day before.

I am so grateful if you can help me.

——————————
Joao Ramos
——————————
——————————————-

[John Thomas]

Happy to help.  Can you provide some sample data?  Without data all I can do is a ques at what you need. (See attached.)  Are the cvp043.xlsx and cvp070.xlsx files the ones that have the daily data you want to accumulate?

Thanks,

——————————
John Thomas
NC United States
——————————
——————————————-

[Joao Ramos]

? 

I use downtime and cvp043, these are updated daily with production information, cvp070 is updated periodically.

I am sending the files, because it contains confidential information, I needed to change some fields in the files.

I couldn’t attach it to the site, so I generated this link for you to check the files.

Thanks a lot for the help.

——————————
Joao Ramos
——————————
——————————————-

[Ben Howard]

Hi Joao,

I am confused – you say that your “only problem is this daily incremental update, where I have to add the one produced the day before.”  Didn’t placing the daily data in a folder work out for you?  Obviously you’ll need separate folders for the downtime and production data….

Also, you have placed your confidential files in a public google folder…

——————————
Ben Howard
United Kingdom
——————————
——————————————-

[Joao Ramos]

Hi ,

I changed the file information and at the end I will remove the link.

My problem is that by placing the previous day’s file in the folder and updating the BI, it overlaps the data, it does not increment.

How do I today not to lose data? I have a file that has the information from 01/01/2020 and I always add the data at the end of it, do the manual increment and the BI every day reads all the lines.

I showed the folder structure, in another image, where the files are generated there.

I’ve done all the necessary configuration:

– RangeStart and RangeEnd parameters;
– Date filter;
– Increment in the table.

——————————
Joao Ramos
——————————
——————————————-

[Ben Howard]

Hi Joao,

So is the query source a folder or Excel file?  From the image you displayed, it looks like the query is from a file.  You need to change the query so that it gets the data from a folder…  

——————————
Ben Howard
United Kingdom
——————————
——————————————-

[Joao Ramos]

I have the separation by folder, where each file is generated.

But as I understand it, I must save all files there, and PB recognizes and does the magic, correct?

I will test it that way.

Thank you very much.

——————————
Joao Ramos
——————————
——————————————-

[Ben Howard]

, That’s correct.  You’ll need separated folders for each data source where you are incrementally adding files. ?

——————————
Ben Howard
United Kingdom
——————————
——————————————-

[Joao Ramos]

Thanks so much.

It’s working now.??

——————————
Joao Ramos
——————————
——————————————-

[John Thomas]

I too am a bit confused.  is correct.  It sounds like you do NOT have incremental files?, but rather you are creating a “cumulative” file.  My suggestion is to stop doing that.  Just save each day’s activities in separate files and note the “run date” or “report date” in the file name like “cvp043 – 2020-08-14.xlsx”.  In power query you can aggregate them all together.  This would make the process much easier.  If your daily exports are text files (csv for example), don’t convert them to xlsx, just save them exactly as they are from the system(s) you are getting the information as csv or txt files and then let power query do it’s magic. 

Generally speaking, the idea is to “don’t touch” any export files from the systems.  Leave them exactly as they are and craft a solution in Power Query to Evaluate, Transform, and Load the data into good tables so you can create a data model.?

——————————
John Thomas
NC United States
——————————
——————————————-