Hi. I've a data file that consists of Year to date (YTD) transactions.
This file is stored in SharePoint and PowerBI Transforms and Loads this file. I built visualization based on that.
Note: I tried to do append, but I noticed in append, I need to apply transformation everytime I add a new file. I already completed transformation of data in the YTD file, and I just want to use same transformation on subsequent montly files.
How did your records store? Since I do not so clear about your table structures, can you please share more details about these?
My data is in CSV file and the headers look as attached.
So both my Year to Date and monthly files have the exact header/layout.
As mentioned, I already built the transformation required to use the YTD data, but now I want to generate monthly incremental files and apply same transformation I did for the YTD data file.
@YJAMOUS if you have all your files in one folder, you can use Get Data->Folder option. This way you can merge all files from the same folder into one table. Every time you put a new file in the folder, on refresh it will automatically pick new file/s and append in the table
That's a good idea and I tried to implement this, but I'll have to build the transformation logic again. Don't I? I already did the transformation for the YTD file.
So you mean your records are stored in different excel sheets with similar structures as your snapshot? (operation date on the top, detail parts are host on the below)
If that is the case, I'd like to suggest adding a new column below to store the header date, then you can merge these tables and they will be grouped based on the 'backorder date'.
Hello @YJAMOUS ,
You can make your power query extraction of csv files generic rather so that it picks up all the files with Extension as csv. The below M query will do the trick
= Table.SelectRows(#"Previous Step", each [Extension]=".csv")
And then to only pick the previous months file, you can setup incremental refresh in Power BI Service.
Please refer this to setup incremental refresh.
Edit: Corrected the link
@YJAMOUS , not very clear, check if these two can help(Dax append)
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
May the fourth be with you, join us online!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.