Upload weekly extracted excel files and show progress

[YasirKazi77]

Hi,
I extract a file from a system that lists the weekly progress of Team objectives. It includes the below columns (it doesn't include any date columns) : 

Week 1 extraction : (File Name : Objectives 20220201)

Week 1 extraction : (File Name : Objectives 20220208)

I want :

1) Upload each weekly file into PowerBI

2) Extract the date from the file name.

3) Show the Historical Weekly Progress for each Objective and the Average progress across all objectives (something like below. The dates are in the report are the dates on which the file was exported (taken from the File Name)

 Updating Media

[Anonymous · Accepted solution]

Hi  @YasirKazi77 ,

Put both Excels into the same folder.

Here are the steps you can follow：

1. Use Power BI to connect to a folder, click Transform data to enter Power query.

2. In Power Query, click Home - Advance Editor. Put this code in. (hint: replace the file name with your own file name path)

let
    folder = "C:\Users\xxx\xxx\excel folder",
    Source = Folder.Files(folder),
    RemoveUnused = Table.SelectColumns(Source,{"Content","Name"}),
    Addtables = Table.AddColumn(
        RemoveUnused,
        "Custom",
        each Table.PromoteHeaders(Excel.Workbook([Content]){[Item = "Sheet1",Kind = "Sheet"]}[Data])
    ),
    Addtables2 = Table.AddColumn(
        Addtables,
        "Custom2",
        each Table.PromoteHeaders(Excel.Workbook([Content]){[Item = "Sheet2",Kind = "Sheet"]}[Data])
),
    #"Removed Columns" = Table.RemoveColumns(Addtables2,{"Content"})
in
#"Removed Columns"

Result：

3. Click [Custom] to expand the chart and select two columns

Result：

4. Result.

put into the matrix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly