Forum Discussion
Data Inconsistency excel files different structure - build a model
HI Anonymous ,
For large amount of randomly structure files, I'd like to suggest you do these transform/conversation by external tools instead of M query code. (notice: these M query codes of transform are complex, low performance and hard to maintain, you need to manually fix these steps codes every time major data structure changes)
In fact, current I haven't found any simple solutions to automatically handle fully randomly data structure formatting, maybe you can try to machine learning?
Guide to File Formats for Machine Learning: Columnar, Training, Inferencing, and the Feature Store
Regards,
Xiaoxin Sheng
When you refer yhqt the fike as inconsistency is that every download is different or the files are always in the same format although is not a table format?
If the files are always downloaded in the same format you can make the query to automate the cleaning of the files to become a table format.
If you can share a sample I can make a small query to exemplify what can be done.
Regards,
MFelix
Hi MFelix
Thanks for your email,
Please see below 2 samples, both of the files are regarding to the same Project and the app provides this files in excel but in different format.
https://drive.google.com/file/d/17hJ32NxKC3Rz1f5ze7nxMtiJ472eJLWq/view?usp=sharing
https://drive.google.com/open?id=1Mbrh7IkGkxTAPrFPYp2BaFw8h4QiT8PD
Would be a good solution if the query can do the ETL and bring into the model in just one shape for all the following files.
Fabricio