Error while combining different excel files

[Anonymous]

I want to upload excel files from a folder. All the files are in the below format.

And I want the result in "PowerBI-Query editor" as below :

But when I tried to combine and load the files using “Get Data”, datasource as a folder, I get the error either one of the files. I am not able to combine both the excel files either using “Unpivot Columns” or “Transpose”.

Any solution regarding the above issue ?

Thanks 

[Anonymous]

Looking at the data i'm expecting you will have a number of problems to resolve once you get each excel file into your Queries section.  Have you done any data cleansing?  What cleansing have you done?

Here are the things you are likely going to need to do among any other challenges you find in your data:

• Remove the Top X rows to remove the fluff at the top
• Copy Down values in your first couple of columns as the pivot table style data is likely to only exist in the first row that it is printed
• You'll need to run an unpivot operation to get the data where you need it to be before merging
• After merging you would need to run a pivot operation to get each category as a column header.

[Anonymous]

Hi Ross73312,

Thanks for reply. I have followed the below steps :

1. The excel files are fetched from a folder. So the GetData -> Folder-> Combine& Edit

2. Choose "First File" as an example file. Click OK.

3. I removed top 6 rows from the "Transform Sample file from Test Report". Then "Use First rows as Headers" followed by "Unpivoting the Columns".  Now I am getting error related to 2nd excel files and not able to find data related to it.

1st excel sheetI am not sure what step I need to follow to merge/ combine both the files without any errors. 

Thanks

[Anonymous]

My recommandation is to bring each file in one by one.  Get each one into the format you are happy with.  Do an append only after you have checked each file is now in the desired matching format.  This will let you step through the problems more easily if they arise.

[Anonymous]

Hi,

In future we are expecting more excel files to be updated in the folder. So bringing each file one by one, transform each files and appending will be repetitive task and I am trying to avoid the same. 

Thanks 

[Anonymous]

Based on your screenshots, your files differ in same fashion.  From what i can see you need to remove the top X rows, but X is different in each file.  You might find you have to deal with each file individually to hand code the required transformations.

[Anonymous]

Hi Ross73312,

What if the "top X" rows are same for both the files? I am getting the same error.

[Anonymous]

If you used the UI to get you started, it would have created for you several functions under "Sample Query".  We can use these to form the basis of your solution.  The one labeled "Transform Sample File from <YourFolder>" is the first part to concentrate on.  If you click on it, you'll see that it has a Source step and then space to do your remove Top X rows.  So go ahead and add that in.  Then premote headers if needed.

Once doing this, you will see an error in your generated table (named after your folder).  For me, when i tested this, this was simply the "changed type" item at the end, which i removed.

Everytime you add a new file to your folder, you'll need to update the Power Query code in this table to look out for the new file.  I have seen threads in the past of people looking for dynamic way to do this.  I'm not sure of the specific solution on that, as I would be loathed to say it can't be done.