I have some excel files that come in different flavor, some of them will be unpredicatable, they all contain the same information but the formats can vary, making it hard to extract the infromation and put it into a master table. But there are some prevalent types none of which is that hard to process in power bi. I will show 2 types here and the expected results. I would know how to recognize the types of forms I will be showing with code but I would like to learn how to do these things with power bi, the modern way.
1.a Can power query get all of the files, from a given folder (https://powerbi.tips/2016/06/loading-data-from-folder/)
1.b. so that it is ready to look at each one and decide what to do. (not sure)
2.a. Can power query BI then have some canned schemas in mind (https://www.thebiccountant.com/2020/07/15/transform-a-query-into-a-function-in-power-query-and-power...)
2b. and can it decide which ones to used based on what it sees, in a given file? (not sure)
- Also I do not know how to copy information from a set of cells into another row? (https://community.powerbi.com/t5/Power-Query/Copying-value-to-another-row-based-on-another-column/m-...) and other hits from google on messy files.
For instance, here is one common type of file?
https://www.dropbox.com/scl/fi/5mdj6tbt29lfqs5j8xhiv/BE-WISE.xlsx?dl=0&rlkey=tqwmowucc6q8btz3a73rvwd...
For the above, can power bi recognize that
- this as a table that has no code info in 3 rows
- if it can realize that this is a certain type of file, then it go ahead and copy the code infor from the "this is a note" row, into the following 3 rows,
- then delete the "this is a note row"
The expected result should look like this:
https://www.dropbox.com/scl/fi/eltecp9udz274pdrpenf7/BE-WISE-intended-result.xlsx?dl=0&rlkey=y6ufcjf...
Here is another type:
https://www.dropbox.com/scl/fi/h055pmuc1i3om3uqvjd8m/BILL-MOON.xlsx?dl=0&rlkey=7732iqgzxa39ootcwkkm...
The expected result should look like this:
Note that
- "5 x $110.00" was concatenated to the row above
- and the amount was also moved to the row above.
https://www.dropbox.com/scl/fi/o2y8oxhq8jexgiindgbu5/BILL-MOON-intended-result.xlsx?dl=0&rlkey=hlg5...
Lastly, I would like to extract information from each cleaned up form and put it into a table:
Expected result:
https://www.dropbox.com/scl/fi/8pnuo06f7iyhxuukfzvak/table-intended-result.xlsx?dl=0&rlkey=u7nvwwqkt...
Any help with this will be appreciated,
Thanks,
SE
