How do I append a refreshed file to previous data without losing original data?

[dmoskowitz]

I have a source file that gets refreshed every month with new data.  Below is my current flow. It opens a file and then it separately opens the folder containg the file to capture the file details. I then add the Modified Month to my original file. This way I can flag the month the file was created. 

Every month the same file gets updated an I would like to add the new month data to the previous month data. So I run PowerBI in November, I get the data with an additional field Modified Month that says "Nov". In December, the same source file gets overwritten with december data. I would like to append the December data to the November data where it will also have the Modified Month field but will say "Dec". 

I am using Power BI Desktop.

CAPEX01 Query

let
Source = Excel.Workbook(File.Contents("C:\desktop\data\Data File.xlsx"), null, true),
Capex01_Table = Source{[Item="Capex01",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Capex01_Table,{{"BU NAME", type text}, {"UNIT NAME", type text}, {"HFM_ENTITY", type text}, {"CUR", type text}, {"MONTH", type text}, {"TTL_UNEXP_LCL", type number}, {"TTL_UNEXP_USD", type number}, {"CER #", type text}, {"DESCRIPTION", type text}, {"YEAR AUTHORIZED", Int64.Type}, {"ORIG AUTH CARRY FORWARD PRIOR YEAR", type number}, {"ORIG AUTH CURRENT YEAR", type number}, {"OVER (UNDER) SPENDING CARRY FORWARD PRIOR YEAR", type number}, {"OVER (UNDER) SPENDING CY", type number}, {"OVER (UNDER) SPENDING OVER/(UNDER) %", type number}, {"EXPENDED DURING PRIOR YEAR", type number}, {"EXPENDED DURING CURRENT YEAR", type number}, {"UNEXPENDED AMOUNT", type number}, {"CLOSE-OUT DATE", type text}, {"BU COMMENTS", type text}, {"ADD'L COMMENTS", type text}, {"UNEXPENDED IN USD", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(Source, "Custom", each Query1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Custom"}, {"Custom.Custom"}),
#"Expanded Custom.Custom" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Custom", {"Modified Month"}, {"Custom.Custom.Modified Month"})
in
#"Expanded Custom.Custom"

Query1 referenced in the above code

let
    Source = Folder.Contents("C:\desktop\data"),
    #"Added Custom" = Table.AddColumn(Source, "Modified Month", each Date.MonthName([Date modified]))
in
    #"Added Custom"

Thank you for your help!

[parry2k]

@dmoskowitz not sure if you want to go that route, I will still stick to the original solution I posted.

@negi007 I already proposed this solution. FYI

[dmoskowitz]

Thank you.

My original folder has a single file though and that file gets updated every month. I am not sure how your solutions accounts for that. The source file, let's say source.xlsx, does not have a date field in it. I need to add the date field based on when the file was generated. The next month, source.xlsx gets overwritten with new data and I need to append that, along with the generated date field, to last months data.

Don't I need to export the data each month as a new file name and then append all the exported files in that folder?

Am I missing something?

[parry2k]

@dmoskowitz anytime, good luck!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dmoskowitz]

So from my research, it looks like I have to use R to export the monthly report after bringing it into Power Query. Is there another way without R? Thanks!

[negi007]

@dmoskowitz one suggestion I would like to give you

1. You create a folder and put all your files in that folder

2. You can specify the name of each file with date like 01.Jan.2021_filename for Jan'21 data and so on for other months data

3. In power query you can import data using data from folder option. when you import data, you also get a column having name of the file. You can in power query, use the file name to create a date column

4. when you combined all your files, it will have a date column which you can use to view monthly data. this way every month when you refresh, it will automatically add the latest month data.

let me know if this works for you.

[dmoskowitz]

Thank you. The source data file name always has the same name and gets overwritten each month. We need a solution where everything gets done in PowerBI/PowerQuery so there is no option to rename the source file each month. That may be an easier solution, but we need to limit human interaction as much as possible

[parry2k]

@dmoskowitz you can have files in the same folder and read all files from the folder, yes you can have one historical file assuming it has a date column in it to separate the data by period and as new files come in, it will read the folder and append the files together.

There are tons of blog posts that you can search that will get you thru the solution. If you are not able to figure it out, let me know.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dmoskowitz]

Thanks. So right now, I have the source file alone in a folder. Reason being is that my second query, Query1, creates a table using the "From Folder" option as it is the only way I could figure out how to extract the file date. If there are other files, it forces me to append them.

I'll keep on checking for other methods before asking you for more help 🙂

Thanks!

[parry2k]

@dmoskowitz you should keep each month file separate and append these together, there is no method to save the data if the original data files get overwritten.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dmoskowitz]

Thanks. What would be the best way to do that? Export each month as a new CSV in a separate folder?Also, what would be the best appraoch for the initial pull where there is no existing export to append to, like when we start in January?