Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Charu
Post Patron
Post Patron

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

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

1 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"

Capturexxxx.JPG

Captureyyyyy.JPG

Final_OutputFinal_Output

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

@Charuyes it is possible in M(agic).

 

Use a folder connection and not file connection.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Dear @smpa01 

 

Could you please help me how to achieve this?

@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"

Capturexxxx.JPG

Captureyyyyy.JPG

Final_OutputFinal_Output

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hi @smpa01 

 

Thanks a lot for sharing solution. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.