Power Query: Get File Name

[skhemka]

Hi guys, I am a teacher and I teach different subjects with different assessment types.

I want to combine all the marks in a single spreadsheet and then process the grades in one go. 

I am struggling to merge the attached sample files. Using Power Query, I am able to establish a connection between the two and then append the query into a single file but I don't know which subject it is.

Therefore, I need to add the file name as a column in the query.

Any help would be greatly appreciated.

Thank you.

[#2]

@skhemka 

You may use not From File->From Workbook connector, but From File->From Folder (or From Sharepoint Folder). Here you may filter on file, take file path and it's name into the variable, expand file content and after that add column with filename from above variable.

Or that could be done automatically with From Folder connector expand content for few filtered files with the same structure.

[#3]

@skhemka Please see the sample workbook. You will have to do the following steps:

1. Save your mark workbooks as .csv files in a specific folder containing only those mark workbooks that you want to combine

2. Save my sample workbook as your workbook then Open Advanced Query Editor and change the folder path in "Source" line to your folder path (note: this is folder path only, not file path)

[#4]

@hynguyen Thank you very much for your reply.

But I do not want to combine the other columns. I want to keep Quiz, Assignment, Test and Exam all in separate columns.

I would appreciate your help, please.

Thank you.

[#5]

@hynguyen Dear hynguyen, please find attached what I have done.n As you can see, the "assignment" and the "test" columns are missing for the second spreadsheet. Any help will be greatly appreciated. Thank you.

[#6]

@skhemka 

With merging of two queries, I guess FullOuter join to have all records from both, it's not clear where to add filenames - in one record it could be both name of first table file, as well as second one.

[#7]

@skhemka Can you please show what your desired output should look like by manually typing in a sample data with all needed columns? It seems that you want to have a layout like: Source, Student ID, Exam, Quiz, Test, Assignment.

However, like @Sergei Baklan mentioned, it is difficult for us to imagine how to treat the case where the same Student ID attends both courses? How do you want to show those cases?

[#8]

@skhemka 

I found no duplications. When it could be

let
    folder = "C:\Test",
    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])
    ),
    RemoveContent = Table.RemoveColumns(Addtables,{"Content"}),
    ExpandContent = Table.ExpandTableColumn(
        RemoveContent,
        "Custom", {"Student ID", "Quiz", "Exam", "Assignment", "Test"},
        {"Student ID", "Quiz", "Exam", "Assignment", "Test"}
    )
in
    ExpandContent

[#9]

@Sergei Baklan Thank you very much for your help here. I am a novice Power Query user and couldn't work out what you have proposed here. Instead, I found a solution that works for me. I added a new column using the index (custom) function (with 0 increments) and then added the code name as a prefix.

[#10]

@skhemka 

I see, thank you for the update. By the way, Index with zero increment that's just a constant, e.g. number 1. If so, you may add custom column in one step as ="Code1" or so.