[HELP] Combine excel files after cleaning.

[Anonymous]

Hi Guys, 

I need some ideas/help.

I have a one drive business folder which the user is uploading files monthly to it and all the files have the same structure or at least we have asked for.

I already did in Power query the upload process to be automatically and the files are being loaded as Union which is good. but I need to do this little clean for the files.

Is there anyway to do the cleanse for one file and then automate for the next ones? the user will put a new file in this folder when they have a new project.

One sample of the file below

basically I need to clean the empty rows and unpivot.

Any idea/help would be very appreciate.

Thanks all

[amitchandak]

@Anonymous , if you have configured it has a folder, steps should repeat on new files.

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

[Anonymous]

Hi, 

Thanks your replying.

I'm having issue after I transpose the data, when I do the merge is not working anymore I think because it looses the Source.Name column,

however the cleaning works for all the files that I upload after if I don't use tranpose.

Any help?

[v-yiruan-msft]

Hi @Anonymous ,

Did you get any error message when you merged these files after transposed? If yes, could you please provide the related screen shot with error message and the codes in Power Query Advanced Editor? Thank you.

Best Regards

Rena

[Anonymous]

@v-yiruan-msft Hi

Thanks for your message,

Actually there's no error, it just doens't bring the files below, if I don't clean at all and use only transpose the files start coming in a new columns instead on the bottom rows, which does not work for me...

[v-yiruan-msft]

Hi @Anonymous ,

Whether you clean all of the files in that OneDrive folder and they still keep same structures after cleaned? Which method you are using to combine these files? Merge or Append? You can refer the following documentations about the details of combine queries in Power Query. 

Append vs. Merge in Power BI and Power Query

Merge Vs. Append In Power BI In Power Query Editor 

In addition,Could you please provide some screen shots to help us get a good understanding for your problem if it is convenient? Thanks you.

Best Regards

Rena