Forum Discussion
skhemka
May 31, 2020Copper Contributor
Power Query: Get File Name
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 struggli...
SergeiBaklan
Jun 05, 2020Diamond Contributor
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
skhemka
Jun 08, 2020Copper Contributor
SergeiBaklan 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.
- SergeiBaklanJun 08, 2020Diamond Contributor
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.