• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data from Different ScoreSheet

S

SanthoshG

New Member
Hi,
I'd like to request your help for a project.
I have scoresheets of the students from a school in different workbooks (each week the school is held, there is a separate Excel file for that week). Is there a way to extract the data and get the total score of each student into one sheet?

Example- Sample 2.xls and Sample 3.xls files attached are almost identical. Except that in Sample 3 some more students came to class. I need a way to get the total (or Year To Date) score of say student "Blake" or student "Joshua".

Please advise.

Thanks,
Santhosh
 

Attachments

  • Sample2.xlsx
    319.3 KB · Views: 7
  • Sample 3.xlsx
    319.5 KB · Views: 10
Here you go.

IMPORTANT: Keep master file in separate folder than source files.

Step by step explanation
1. First added "Import" sheet to each source file. Dead copy of Name & Score column from "SPARKS-WINNER" sheet. Also added Team & Date column. This sheet will be imported into master workbook.

2. From PowerQuery ribbon tool. Import from File->Excel and choose one of the workbook.

3. Choose "Import" sheet and click on Edit.

4. In Advanced Editor, add "(filePath, shtName)=>" before "let" and put variables into appropriate line (Source & ImportSheet).

M after edit.
Code: 
(filePath, shtName)=>
let
    Source = Excel.Workbook(File.Contents(filePath), null, true),
    Import_Sheet = Source{[Item=shtName,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Import_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Score", Int64.Type}, {"Team", type text}, {"Date", type date}})
in
    #"Changed Type"

5. Rename the function to "funcImport" and close and load as connection only.

6. Go to PowerQuery ribbon tool and Import from File ->Folder and choose the folder where all source files will be located (C:\Test\Sparky in sample) and hit "Edit"

7. Transform Extension column values to "UPPERCASE" by right clicking on column and choosing Transform option. This step is needed when there could be mixed file types and extension in the folder.

8. Filter Extension column for Contains "XLS". To filter out any unwanted files.

9. Remove all but the Name & Folder Path columns.

10. Add custom column with following formula. Where [] are column names and "Import" is the sheet name of source that's being imported
Code: 
=funcImport([Folder Path]&[Name],"Import")

11. Expand the custom column, making sure to uncheck "Use Original Column...".

12. Clean up and transform data as needed (In the sample file, use Query Editor to follow each transformation step applied).

Final M for the folder Query.
Code: 
let
    Source = Folder.Files("C:\Test\Sparky"),
    #"Uppercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Upper}}),
    #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each Text.Contains([Extension], "XLS")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each funcImport([Folder Path]&[Name],"Import")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Score", "Team", "Date"}, {"Name.1", "Score", "Team", "Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Folder Path", "Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name.1", "Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Name", type text}, {"Score", type number}, {"Team", type text}, {"Date", type date}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Name", Text.Trim}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Name", Text.Clean}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Cleaned Text",{"Name", "Team", "Score", "Date"})
in
    #"Reordered Columns1"

Result should look like...
upload_2017-3-15_12-26-39.png

Now you have multiple option to summarize. The sample has first option.
  • Just load the table to sheet and use standard pivot table to summarize data
  • Use GroupBy and other aggregation within PowerQuery to summarize data
  • Use PowerPivot add-in to bring in data query into data model and summarize
When you refresh data, it will dynamically pull info from the folder (as long as first step is followed and "Import" sheet is added to each).

BONUS:
Add Named Range and use it to specify folder as parameter (see Param sheet in sample)
1. In a cell enter folder path (Ex: "C:\Test\Sparky") and name that range as "foldPath" using name manager.

2. Edit the folder query using Advanced Editor and add variable like below. Add fPath line and edited Source line. No other change needed.
Code: 
let
    fPath = Excel.CurrentWorkbook(){[Name="foldPath"]}[Content]{0}[Column1],
    Source = Folder.Files(fPath),

Now if folder where source files are kept changes, you can just edit the value in a cell and query will use it as parameter.
 

Attachments

  • Test.zip
    198.7 KB · Views: 4
Wow. Thanks Chihiro for the detailed explanation. I will give it a try on my laptop. (With this desktop being Vista OS, it does not let me download PowerQuery).
I'll keep you posted on how it goes. Thanks again!
 
Chihiro,
Unfortunately PowerQuery does not work on my laptop either. It keeps giving an ActiveX error that I can't even close (have to kill Excel each time). Is there any other way to do this via VB script or any other method?

Thanks,
 
You must log in or register to reply here.
Back
Top