Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
My requirements:
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.
Hi @Anonymous,
How did your records store? Since I do not so clear about your table structures, can you please share more details about these?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
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.
@Anonymous 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
Proud to be a Super User!
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.
Hi @Anonymous,
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'.
Regards,
Xiaoxin Sheng
Hello @Anonymous ,
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
can you check if the link is correct?
Please check now
@Anonymous , not very clear, check if these two can help(Dax append)
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |