Get data from folder issue.

[T-Pan]

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?

 

  

 

[T-Pan · Accepted solution]

@spinfuzer @m_dekorte , in tranform sample query, i deleted the promote to headers step and add another step of "delete the first row". So now i have column 1, column 2, etc,  as headers and in my appended query  renamed the columns as i want. I don't know if its the right way but it works.

[spinfuzer]

That sounds good.  All you need to do is make sure you have the same column names one way or another.

[m_dekorte]

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.

[T-Pan]

Tomorrow morning will ne the first thing that i will do when i arrived to the office, be sure about that. 😛

[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".

[T-Pan]

But again its different Column name from the other Excels as you can see from images.  Will ot work?

[spinfuzer]

You need to edit the Transform Sample Query and edit the Main Query because the Main Query might be broken because the column name(s) are different.   You can probably just delete the steps with errors.

[spinfuzer]

Is Column2 always "Date Missing"?  Date followed by a space then Missing?  If so, yes, it will work.

[T-Pan]

Yes the format o column 2 name is always the same. I've asked the it department to named it "missing" and create another column with "Date". But demands to IT Department always take a lot of time to be made.