Vattenfall-Don_t_Build_on_Sand__The_Case_for_Solid_Data_Foundations__yes__with_Excel_.pptx.pdf

Transcript

1. Confidentiality: C1 - Public Vattenfall Breaking society free from fossil fuels
2. Confidentiality: C1 - Public Don’t Build on Sand: The Case for Solid Data Foundations (yes, with Excel) Elise Teixeira Plan & Support Data Engineer Ulf Stolzke Director External Workforce
3. Confidentiality: C1 - Public Agenda ▪ This is Vattenfall ▪ Pillars that support a solid data-driven transformation ▪ Excel as a Data Engine ▪ Q&A
4. Value chain Production Vattenfall generates electricity from many types of energy sources. We are actively phasing out fossil fuels and investing to expand renewable generation. Electricity distribution Guarantee secure supply requires well-functioning distribution networks and development of smart network solutions. Vattenfall also enables customers to feed self-generated electricity into the grid. Sales of electricity, heat & gas Sells electricity, heat and gas to consumers and business customers. Focuses on various price and service models. We give customers the opportunity to understand and reduce their environmental impact. Energy services and decentralised generation Battery storage, network services, charging solutions for electric vehicles etc. We also provide market-places where customers can buy and sell electricity, as well as solutions for customers to optimise their energy use. District heating One of Europe’s largest producers and distributors of district heating, supplying households and industries in metropolitan areas. In partnership with cities and regions we are driving the transformation towards fossil-free heating solutions.
5. Key KPIs 100% Owned by the Swedish State 7.7 million Electricity customers 1.0 million Electricity network customers 0.6 million Heat customers 2.4 million Gas customers 20,665 Employees
6. Confidentiality: C1 - Public Pillars that support a solid data-driven transformation Be Open & Curious Empathy Understanding your process & your data Get curious about the people, as if you did not know anything about the company. Opening questions look like: - What do you always need to answer? - How do you know when you reached a good result? And a bad one? - Which part of the system you check first when analysing X scenario? - What could potentially improve your work routine, even if you think it`s impossible? Answering what they ask and surface what they need. A great data solution doesn’t just respond to a question; it also reveals the dimensions your stakeholders didn’t realize existed. It connects the dots, anticipates the context, and provides clarity from different angles. From mapping your process to modelling your data, using Excel can help you to understand: - What each column represents - Key process signals - How your data is profiled - The Golden Keys Strategy - The health check of your data
7. Confidentiality: C1 - Public Understanding your process and your data The Golden Keys Strategy What it is 5–7 rows chosen with stakeholders Each row = a critical business scenario Validates formulas, joins, timing & logic Why it matters Reflects business reality Builds trust with stakeholders Surfaces blind spots & exceptions How it works Collect real scenarios Test in Excel / Power Query Spot breaks in logic Adjust until all keys unlock - Framework that increases data trustworthiness - Compass for logic testing - Bridges business ↔ data - Foundation for automation & AI readiness
8. Confidentiality: C1 - Public Excel as a Data Intelligence Engine Understanding your process & your data Before we start with modelling, we need to think about where the data comes from, and how to make that process reliable, consistent, and scalable. That thinking starts with Power Query, an Excel’s built-in data engine .It connects to flat files, to cloud folders, relational databases, web APIs, and enterprise dataflows, transforming Excel into a data integration tool. 1. Go to Data tab 2. Select Get Data 3. Click on From File 4. Select From Excel Workbook 5. Choose your file location and your file 6. Click on Transform Data 7. You will see your data in a table, ready to be transformed Eliminate copy and paste of manual data Connects to centralized sources Enables repeatable and scalable transformations Clean modelling & Automation
9. Confidentiality: C1 - Public Excel as a Data Intelligence Engine Understanding your process & your data Power Query Functionalities Study your data 1. Go to View tab in Power Query 2. In Data Preview, check: • Column Quality • Column Distribution • Column Profile It will allow you to see valid data types, errors, empty, cells, unique values, values distribution and column statistics. Filter & Clean, Split & Combine Remove columns, filter by condition, remove duplicates, clean texts, transform data types, split column by delimiter, extract date snapshots, merge columns, format data Group & Aggregate Group by (e.g., hours, months, diverse categories can be used), aggregate data ( e.g. max, average, distinct count), create logic for subtotals and summaries Add Logic Columns Use Conditional Columns to represent the variables of your process (IF… THEN… ELSE). Create custom columns using formulas, or calculated columns based on other fields. Index or rank your rows. Join data sources Use Merge Queries to bring data from different sources, choose the join type: left, right, inner, etc. Or Append Queries to add data to your data source - very useful for keeping history of different snapshots. Keep track of your process Rename each steps in the Applied Step section to represent truthfully which part of the process you are aiming with the data transformation.
10. Confidentiality: C1 - Public Excel as a Data Intelligence Engine Understanding your process & your data Query Table You now have your data built with your logic in a Query table. You can use it to perform mass logic in your entire data: ▪ IF/SWITCH: multi-conditional rules, binary processes, ▪ AND/OR: combine multiple conditions for approval logic ▪ VLOOKUP/INDEX-MATCH: test relationships between tables ▪ TEXT/CONCATENATE: create workflows notes, combine strings ▪ COUNTIFS/SUMIFS/MAXIFS: perform high quality aggregations based on process conditions ▪ RANK: prioritize and sort items ▪ DATE/DATEDIF/TODAY: handle dates, scheduling, deadlines, SLA tracking Once you load the query into Excel, you have/can: Pivot Table/Power Pivot A PivotTable can help you to summarize, explore, and interact with data: ▪ Drag and drop fields to analyze different combinations ▪ Apply filters, slicers, and timelines ▪ Create dynamic reports for dashboards ▪ And when backed by Power Pivot, it becomes even more powerful Power Pivot will help you to model your data and make it organized: • Load your Power Queries into your data model • Have your data model in a diagram view • Create relationships between tables • Build dynamic DAX formulas/measures that react to different slicers and filters Via: Data -> Data Model -> Manage Data Model VBA VBA turns repetitive tasks into one-click solutions, it can help you with: ▪ Refresh all queries at once ▪ Format and export PDFs, Word documents, or Excel with snapshot of your data ▪ Generate different reports for different stakeholders ▪ Open Outlook and write personalized e-mails ▪ Track and perform end-to-end processes ▪ Create a front-end for your data and processes, where users will find it easy to visualize, interact with and bring in more data VBA is not the easiest feature in Excel ,as it requires coding, but if you're on the road to data transformation, it lets you automate repetitive tasks and free up your time to focus on the next step of your journey. My suggestion is to use the ChatGPT “Genie –to help you build whatever you need. elise.suedekumteixeira@vattenfall.com
11. Confidentiality: C1 - Public Q&A