Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Standardize a File

Brass Contributor

Hello!  I have a bit of a problem... I have over 30 files that have either one or another format (based on time).

 

I am wanting to standardize the data to input into powerBI.  I have all of these files in on location on a SharePoint site, but need to get them all in the same format to be able to combine them in PowerBI.  I'm not sure what the best way to go about this is.  

 

Attached are two files that are the two different formats (different by a few columns having to do with COVID).

 

Any advice/tips on how to combine this data is MUCH appreciated!  I would really love to not have to manually manipulate each file.

1 Reply

Hi @nattiej101 

 

I see you raised 'a bit of a problem...' twice (it's useless) already


In principal reformatting data is something that can be done with Get & Tranform (aka Power Query) and maybe with VBA or Office Script

 

I looked your 2 files to get a sense of the feasibility with Power Query. Each file has > 1 sheet and you don't say which one(s) contains the data to 'capture'. If it's sheet 'Assy WA Data' this sounds doable*, for the others this looks (almost) mission impossible IMHO

 

* For sheet 'Assy WA Data' I can take a look at doing it if you indicate which columns should be captured (in each file) + for April 2021 what should be the header for the 1st column:

 

Sample.png


NB: Assuming I can do it for the 2 files you shared, if your other ones are not formatted the same you will have to adapt to queries...