I’m new to Power BI. As an exercise, I thought I’d do some analysis of Lake Mead’s elevation levels so I downloaded this data from a website.
The first problem smacked me in the face immediately.
I’m at a loss for how to import this spreadsheet of Year/Month data in a format I can use. I think I need to generate a new table of individual values with Date / Level columns. If so, then how? I’m not even sure how to retain the proper sequence of months (Jan = 1, Dec = 12, etc) since they’re just characters, much less, how to generate a persisting table from existing data.
Is there a lecture or example someone could reference that walks me through all this?
Here’s the list of transformational steps that you’ll be required to perform in the Query Editor so that further analysis can be carried. Below is the screenshot of the steps alongwith the data model design as well as of final results provided for the reference -
I’m also attaching the working of the PBIX file for the reference purposes so that you can also review the steps by going into the Query Editor. Also to learn more in-depth about the data transformational techniques and data modelling you can go through the course - “Data Transformations and Modelling” which is already availble onto our EDNA education portal. Below is the link of that course provided for the reference as well.
Hoping you find this useful and meets your requirements that you’ve been looking for.
BTW: I scraped the Acer-Foot equivalence tables into this new spreadsheet from this government PDF on Lake Mead and will work on my first crack at visualization solutions where I’m not following someone’s step by step instructions.
Lake elevations (above sea level) aren’t entirely meaningless, but they hide true water reservoir capacity percentages. Strangely, nearly every newspaper or online visualization of the US Western Region severe drought situation uses them.
It might make an interesting topic for some future EDNA challenge if this data were collected from all reservoirs; although Lake Mead and Lake Powell are by far the largest. Max capacity is Approx 50,000,000 acre-feet combined, compared with under 20M for all other major reservoirs West of the Mississippi combined. It’s scary that we’re at about 17M now (combined Mead + Powell) when they were all nearly full not too long ago. It’s a topic that deserves proper visualization since leaders are constantly making multi-billion dollar decisions with grave consequences for half the country’s population & agriculture output. At the least, even if they’re well informed for making the decisions, the public could use proper tools to see where we’re at, predict the future with various sliders, etc.
