Forum Discussion
Helper IGet data from folder issue.
Hello everyone, on my job i have to create a report which will be updated daily with the excels in picture below. As you will see, all columns of these excels have the same Column names except the second column. So if i want to choose to get data from folder it works perfectly for all columns except the second which only returns the values of the excel wich i use as sample. I could also upload every day the excel and append the columns with same name and merged the columns with different names into a new column, but what i am looking for is a more automated procedure through power query and not a daily manual append. Does anyone know a way to solve this?
11 Replies
T-Pan
spinfuzerSolution Sage
That sounds good. All you need to do is make sure you have the same column names one way or another.
- spinfuzer
Edit the Sample File.
Demote Headers
Extract Text After Delimiter Space on the second column
Promote Headers. The column name is now just "Missing".
- m_dekorte
Resident Rockstar
Alternatively you can transform column names, that will look something like this:
Table.TransformColumnNames( PrevStepName, each if Text.Contains(_, ".") then Text.AfterDelimiter(_, " ") else _)
And if as you say it is always the second column, you can easily extract that date and add its value to a new column in the table. That will look something like this.
Table.AddColumn( PrevStepName, "Date", each Text.BeforeDelimiter( Table.ColumnNames(PrevStepName){1}, " "))
- T-Pan
I do this after i have combined the excels in power query?(get data from folder / combine and tranform)
- m_dekorte
No, you would add that to the Transform Sample Query. If you share the code that's in that Transform Sample Query, we can help incorporate that rename step and optionally the add column step as well if desired.
