cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YJAMOUS
Helper I
Helper I

Appending new CSV to existing file

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:

  1. Instead of asking the users to extract each month the YTD transactions, I would like to only get the last month transaction and append it to existing data.
  2. Also, I want to make sure that I use the SAME transformation rules that I used in the initial YTD data.
  3. Any suggestions here?

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.

9 REPLIES 9
v-shex-msft
Community Support
Community Support

Hi @YJAMOUS,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

 

 

Data Example.PNG

negi007
Super User II
Super User II

@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

negi007_0-1614699453277.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


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 @YJAMOUS,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
PC2790
Solution Sage
Solution Sage

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 

can you check if the link is correct?

Please check now

amitchandak
Super User IV
Super User IV

@YJAMOUS , 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-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors