Forum Discussion
Data consolidation across multiple excel sheets
Hello,
I have a somewhat challenging data set which I'm wondering how it's best to tackle.
There's a single excel file, with multiple sheets.
Each sheet has a first column that states product IDs, and the columns themselves are each headed by a date, like so:
Sheet 1: 01/01/19, 02/01/19.....15/05/19
Sheet 2: 16/05/19, 17/05/19 ... 20/07/19
Sheet 3: 21/07/19 ... and so on
The tables are populated by values per product per date.
I want to create a single database that would show me all products (they aren't necessarily identical between sheets), and their value per date. Preferrably in chronological order.
Can anyone share suggestions for the best way to tackle this?
Thanks!
1 Reply
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, I believe your first step should be to unpivot all of the date columns. You should end up with the product name, date and value in each row. I would then consider appending all of these sheets together into a single table. At that point things should be much easier to work with. You could use something like EARLIER in a calculated column to show you differences across dates. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
