Auto Merge excel data in to one file monthly in power BI

[Charu]

Dear Community,

Is it possible to auto merge  the excel data every month or every new files in the folder - in to single file which has been connected as a data source already?

For instance I have connected File1 excel data source in power BI for this month.Upcoming months if I place a new files in the same folder then those new files data has to be merged with the File1.So that When I schedule for data refresh newly added datas will reflect in to the report.

Is this possible in power BI?

Looking forward to hear from you all !...

Thanks in advance

[smpa01]

@Charuyes it is possible in M(agic).

Use a folder connection and not file connection.

[Charu]

Dear @smpa01 

Could you please help me how to achieve this?

[smpa01 · Accepted solution]

@Charulet's suppose you have a folder called "New Folder" in the following location=> C:\Users\XXX\Documents\New folder where you are storing all the files as following and each of those files have 2 columns called Index and Name. You want to run a code that picks whatever the filder contents are and sew them together to generate an unified output.

let
Source = Folder.Files("C:\Users\XXXX\Documents\New folder"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data"}, {"Name", "Data"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Name] = "Sheet1")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Index", type any}, {"Name", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Index] <> "Index"))
in
#"Filtered Rows1"

Final_Output

[Charu]

Hi @smpa01 

Thanks a lot for sharing solution. 🙂