How to append automatically new sheet into the historical table

[Anonymous]

Hi all,

I have the following scenario:

1) Every day there is a new sheet saved in the same folder

2) I need to append this sheet into a historical table

I am extracting the files using the folder option and sorting the last file saved in the folder to get the last one. However, I'd like to know how can I append automatically this new file automatically into the history table or even better if there is a better option. Maybe using the incremental extraction?  Thanks in advance!

[mussaenda]

Hi @Anonymous,

if you are retrieving the file using folder, 

do not filter it by latest file. retrieve all.

Then after doing ETL, you can refrence the table and filter only to the latest file. (only if you are open to make another table.)

If not open to refrence a table,

use dax to retrieve the latest file.

Hope this helps. 

[Anonymous]

Hi @mussaenda ,

I didn't get your point, are you refering to this approach shared previouslyt by Amit? 

[amitchandak]

@Anonymous , have you already tried the option in this blog?

https://wkrzywiec.medium.com/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711

[Anonymous]

Hi @amitchandak ,

Thanks for your answer, that would be my approach to get the latest file, however, my question is how to append them automatically in power BI, every day, so can build the a historical table. For example, using power query in Excel it is possible doing the following:

1) Create a query extracting the latest file from the folder

2) Copy this query and change the source for the table in excel where you want to keep the historical data:

    As you can see the table in my case is tblAldiDailyScanHist and every day you append the new data

 let
Source = Excel.CurrentWorkbook(){[Name="tblAldiDailyScanHist"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Aldi Ref", type text}, {"Product", type text}, {"Qty (Units)", Int64.Type}, {"StartOfWeek(Sun)", type date}}),
#"Appended Query" = Table.Combine({#"Changed Type", qryAldiDailyScanData}),
#"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([Product Code] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"Date", "StartOfWeek(Sun)", "Mth#", "Year", "Product Code", "Aldi Ref", "Product", "Brand", "Qty (Units)"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Date", Order.Descending}})
in
#"Sorted Rows"

However, when I tried in Power BI, I got the following error:

Expression.Error: A cyclic reference was encountered during evaluation.

What I can try is to create a empty query with the same structure and try this approach