Is it possible to refresh a report using a new excel file with the same table column names?

[Jmole123]

I recieve a new excel data file every week from a company, it contains the same table columns just with new weekly data, is it possible for me to get Power BI to refresh my report using these new excel files without having to update the original file? Am I able to create a folder and have Power BI refresh with whatever is inside that folder? My goal is to update my Power BI dashboard and reports with as little manual labour as possible.  I am new to power BI and I am unsure whether this is possible or not. Any tips on how I could make this work will be great. 

[TeigeGao · Accepted solution]

Hi @Jmole123 ,

In your scenario, we can use the PowerBI folder connector. 

Then select the Combine (Combine & Edit) button:

We will get a table like below, after that, we can delete a column Source.Name:

Close & Apply, we will get a table:

Then, we can add a new file to the folder, refresh it:

The power query like below:

let
    Source = Folder.Files("C:\Users\teigeg\Desktop\Excel"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"})
in
    #"Removed Columns"

Best Regards,

Teige