Exploring the depths of Data Science with Excel: A glimpse into ‚Data Smart‘ by Jordan Goldmeier, a must-read for data enthusiasts.
Data Smart (Amazon) is an exceptional guide that creatively uses Microsoft Excel to teach data science, making complex concepts accessible to business professionals. This 2nd edition, masterfully updated by Jordan Goldmeier, arrives a decade after John Foreman’s highly acclaimed original version, bringing fresh perspectives and contemporary insights to the renowned first edition.
Whether you’re a novice or a seasoned analyst, this book provides valuable insight and skill enhancement without requiring extensive programming knowledge. The practical, problem-solving approach ensures that you not only understand the theory, but also how to apply it in real-world scenarios. That’s why I’ve chosen Data Smart as our latest pick for the #datamustread book club.
Why „Data Smart“ is a #datamustread
Data Smart stands out in the realm of data science literature. Its approachable and practical methodology is a breath of fresh air for business professionals and data enthusiasts alike. Here’s why this book is an indispensable resource:
1. Excel as Your Data Science Laboratory: The use of Excel, a tool many of us are familiar with, to unravel data science concepts is nothing short of brilliant. This approach significantly flattens the learning curve, making complex techniques more digestible.
2. Practical Learning through Real Business Problems: Each chapter of the book introduces a different data science technique via a relatable business scenario. This context-driven approach makes the learning experience tangible and immediately applicable.
3. No Programming, No Problem: The author’s method of teaching data science without delving into programming languages makes the content accessible to a broader audience.
4. Excel Skills Elevated: In addition to data science concepts, readers will enhance their Excel prowess with advanced tools like Power Query and Excel Tables.
5. A Spectrum of Techniques: From cluster analysis to forecasting, the book covers a wide array of methods, making it a comprehensive toolkit for any aspiring data scientist.
6. Fresh Perspectives in the Second Edition: Goldmeier’s updates are not just cosmetic; they incorporate the latest Excel features, ensuring the content remains relevant in today’s fast-paced tech landscape.
Bridging the Gap with „Teach Yourself VISUALLY Power BI“
While exploring Data Smart, you’ll find parallels with the insights shared in my own book, Teach Yourself VISUALLY Power BI. Both texts aim to make data analytics accessible and actionable, providing a solid foundation for anyone looking to make informed decisions based on data.
Your Journey into Data Science Awaits
Data Smart is a gateway to understanding data science through a familiar and powerful tool: Excel. Whether you’re a beginner or a seasoned analyst, this book will enhance your analytical skills and expand your understanding of data in the business world.
Order Data Smart today and support both the authors and my endeavors in bringing such valuable resources to our community. Let’s dive into this journey of discovery together, transforming data into actionable insights.
Join the Conversation
After delving into Data Smart, I’d love to hear your thoughts and takeaways. Share your insights and join the discussion in our vibrant #datamustread community on LinkedIn and Twitter:
Even more Power BI DAX tricks in these books: „Datenvisualisierung mit Power BI“ and „Teach Yourself Visually Power BI“
Power BI DAX (Data Analysis Expressions) is at the core of Microsoft’s Power BI and offers incredible capabilities for data manipulation and insights. In this post, we’ll explore 20 ultimate DAX tricks to elevate your Power BI reports. Whether you’re a beginner or an expert, these tips will help you unlock the full potential of Power BI and Microsoft Fabric.
20 Ultimate DAX Tricks – Simply Explained
Use CALCULATE for Context Modification 🛠️ CALCULATE is a powerful function that changes the context in which data is analyzed. Example:CALCULATE(SUM('Sales'[Sales Amount]), 'Sales'[Region] = "West") This calculates the sum of sales in the West region.
Use RELATED for Accessing Data from Related Tables 🔄 RELATED function allows you to access data from a table related to the current table. Example: RELATED('Product'[Product Name]) This fetches the product name related to the current row.
Use EARLIER for Row Context 🕰️ EARLIER is a useful function when you want to access data from an earlier row context. Example: CALCULATE(SUM('Sales'[Sales Amount]), FILTER('Sales', 'Sales'[Sales ID] = EARLIER('Sales'[Sales ID])))
Use RANKX for Ranking 🏅 RANKX function allows you to rank values in a column. Example: RANKX(ALL('Sales'), 'Sales'[Sales Amount], , DESC) This ranks sales amounts in descending order.
Use DIVIDE for Safe Division 🧮 DIVIDE function performs division and handles division by zero. Example: DIVIDE([Total Sales], [Total Units]) This divides total sales by total units and returns BLANK() for division by zero.
Use SWITCH for Multiple Conditions 🔄 SWITCH function is a better alternative to nested IFs. Example: SWITCH([Rating], 1, "Poor", 2, "Average", 3, "Good", "Unknown") This assigns a label based on the rating.
Use ALL for Removing Filters 🚫 ALL function removes filters from a column or table. Example: CALCULATE(SUM('Sales'[Sales Amount]), ALL('Sales')) This calculates the total sales, ignoring any filters.
Use CONCATENATEX for String Aggregation 🧵 CONCATENATEX function concatenates a column of strings. Example: CONCATENATEX('Sales', 'Sales'[Product], ", ") This concatenates product names with a comma separator.
Use USERELATIONSHIP for Inactive Relationships 🔄 USERELATIONSHIP function allows you to use inactive relationships. Example: CALCULATE(SUM('Sales'[Sales Amount]), USERELATIONSHIP('Sales'[Date], 'Calendar'[Date])) This calculates sales using an inactive relationship.
Use SAMEPERIODLASTYEAR for Year-Over-Year Comparisons 📆 SAMEPERIODLASTYEAR function calculates the same period in the previous year. Example: CALCULATE(SUM('Sales'[Sales Amount]), SAMEPERIODLASTYEAR('Calendar'[Date])) This calculates sales for the same period last year.
Use BLANK for Missing Data 🕳️ BLANK function returns a blank. Example: IF('Sales'[Sales Amount] = 0, BLANK(), 'Sales'[Sales Amount]) This returns a blank if the sales amount is zero.
Use FORMAT for Custom Formatting 🎨 FORMAT function formats a value based on a custom format string. Example: FORMAT('Sales'[Sales Date], "MMM-YYYY") This formats the sales date as „MMM-YYYY“.
Use HASONEVALUE for Single Value Validation 🎯 HASONEVALUE function checks if a column has only one distinct value. Example: IF(HASONEVALUE('Sales'[Region]), VALUES('Sales'[Region]), "Multiple Regions") This checks if there is only one region.
Use ISFILTERED for Filter Detection 🕵️♀️ ISFILTERED function checks if a column is filtered. Example: IF(ISFILTERED('Sales'[Region]), "Filtered", "Not Filtered") This checks if the region column is filtered.
Use MAXX for Maximum Values in a Table 📈 MAXX function returns the maximum value in a table. Example: MAXX('Sales', 'Sales'[Sales Amount]) This returns the maximum sales amount.
Use MINX for Minimum Values in a Table 📉 MINX function returns the minimum value in a table. Example: MINX('Sales', 'Sales'[Sales Amount]) This returns the minimum sales amount.
Use COUNTROWS for Counting Rows in a Table 🧮 COUNTROWS function counts the number of rows in a table. Example: COUNTROWS('Sales') This counts the number of rows in the Sales table.
Use DISTINCTCOUNT for Counting Unique Values 🎲 DISTINCTCOUNT function counts the number of distinct values in a column. Example: DISTINCTCOUNT('Sales'[Product]) This counts the number of distinct products.
Use CONTAINS for Lookup Scenarios 🔍 CONTAINS function checks if a table contains a row with certain values. Example: CONTAINS('Sales', 'Sales'[Product], "Product A") This checks if „Product A“ exists in the Sales table.
Use GENERATESERIES for Creating a Series of Numbers 📊 GENERATESERIES function generates a series of numbers. Example: GENERATESERIES(1, 10, 1) This generates a series of numbers from 1 to 10 with a step of 1.
Want to stay updated with the latest Power BI insights? Follow me on Twitter and LinkedIn. Share your thoughts, ask questions, and engage with a community of Power BI enthusiasts like yourself.
Feel free to leave a comment, ask questions, or share my Power BI DAX tweets:
What is Microsoft Fabric: The Data Platform for the Era of AI
In the complex world of data analytics, a data lake serves as a centralized repository where you can store all your structured and unstructured data at any scale. It offers immense flexibility, allowing you to run big data analytics and adapt to the needs of various types of applications. But imagine having more than just a data lake. Imagine having an entire suite of data management and analytics services that work seamlessly together. That’s where Microsoft Fabric comes in.
Microsoft Fabric is an all-in-one analytics solution designed for enterprises. It spans everything from data movement and data science to Real-Time Analytics and business intelligence. It offers a comprehensive suite of services, including a data lake, data engineering, and data integration, all conveniently located in one platform.
Use Cases of Microsoft Fabric in Data-Driven Companies
Microsoft Fabric covers all analytics requirements relevant to a Data-Driven Company. Every user group, from Data Engineers to Data Analysts to Data Scientists, can work with the data in a unified way and easily share the results with others. The areas of application at a glance:
Data Engineering: Data injected with the Data Factory can be transformed with high performance on a Spark platform and democratized via the Lakehouse. Models and key figures are created directly in Fabric.
Self-Service Analytics: Following the data mesh paradigm, a single data team can be provided with a decentralized self-service platform for building and distributing their own data products.
Data Science: Azure Machine Learning functionalities are available by default. Machine learning models for applied AI can be trained, deployed, and operationalized in the Fabric environment.
Real-Time Analytics: With Real-Time Analytics, Fabric includes an engine optimized for analyzing streaming data from a wide variety of sources – such as apps, IoT devices, or human interaction.
Data Governance: The OneLake as a unified repository enables IT teams to centrally manage and monitor governance and security standards for all components of the solution.
Users can also be supported at all levels by AI technologies. With Microsoft Copilot, Microsoft Fabric offers an intelligent chatbot that translates voice instructions into concrete actions. Developers have the opportunity, for example, to create their program codes, set up data pipelines, or build models for machine learning in this way. In the same way, business users can use the copilot to generate their reports and visualizations for data analysis using voice input alone.
Simplifying Data Analytics: How Microsoft Fabric Offers a Unified, End-to-End Solution
With Fabric, you don’t need to piece together different services from multiple vendors. Instead, you can enjoy a highly integrated, end-to-end, and easy-to-use product that is designed to simplify your analytics needs. One conceivable deployment scenario for the future is data mesh domains with Microsoft Fabric that are connected to an existing lakehouse based on Azure Data Lake Storage Gen2 and Databricks or Synapse. In this setup, the lakehouse continues to handle the core data preparation tasks.
Meanwhile, the decentralized domain teams can use the quality-assured Lakehouse data via Microsoft Fabric using shortcuts to create and deploy their own use cases and data products. Such an approach could prove to be an ideal option, as it optimally complements the advantages of both approaches. The platform is built on a foundation of Software as a Service (SaaS), which takes simplicity and integration to a whole new level.
Microsoft Fabric is not just another addition to the crowded data analytics landscape. Centered around Microsoft’s OneLake data lake, it boasts integrations with Amazon S3 and Google Cloud Platform. The platform consolidates data integration tools, a Spark-based data engineering platform, real-time analytics, and, thanks to upgrades in Power BI, visualization, and AI-based analytics into a single, unified experience.
Microsoft Fabric Pricing Streamlines Your Data Stack for Optimal Cost Efficiency
The rapid innovation in data analytics technologies is a double-edged sword. On one hand, businesses have a plethora of tools at their disposal. On the other, the modern data stack has become increasingly fragmented, making it a daunting task to integrate various products and technologies. Microsoft Fabric aims to eliminate this „integration tax“ that companies have grown tired of paying.
Microsoft Fabric is built around a unified compute infrastructure and a single data lake. This uniformity extends to product experience, governance, and even the business model. The platform brings together all data analytics workloads—data integration, engineering, warehousing, data science, real-time analytics, and business intelligence—under one roof.
Microsoft Fabric introduces a simplified pricing model focused on a common Fabric compute unit. This virtualized, serverless computing allows businesses to optimize costs by reusing the capacity they purchase. The multi-cloud approach, with built-in support for Amazon S3 and upcoming support for Google Storage, ensures that businesses are not locked into a single cloud vendor.
Enhanced Data Governance with Microsoft Purview
Data governance is another area where Microsoft Fabric excels. Using Microsoft Purview, allows businesses to manage data access meticulously. For instance, confidential data exported to Power BI or Excel will automatically inherit the same confidentiality labels and encryption rules, ensuring security.
Microsoft Fabric also offers a no-code developer experience, enabling real-time data monitoring and action triggering. The platform will soon incorporate AI Copilot, designed to assist users in building data pipelines, generating code, and constructing machine learning models.
My Personal Experience so far
Having personally demoed Fabric to over 20 enterprises, the excitement is palpable. The platform simplifies data infrastructure while offering the flexibility of a multi-cloud approach. Most notably, it’s built around the open-source Apache Parquet format, allowing for easier data storage and retrieval.
Microsoft Fabric is currently in public preview and will be enabled for all Power BI tenants starting July 1. The platform promises to be more than just a tool; it aims to be a community where data professionals can collaborate, share knowledge, and grow. So, when someone asks you, „What is Microsoft Fabric?“ you’ll know it’s not just a product; it’s a revolution in data analytics.
Join our Microsoft Fabric & Power Platform LinkedIn Group!
Our LinkedIn group has changed its name to Microsoft Fabric & Power Platform to reflect the evolving ecosystem and the seamless integration between Power Platform technologies like Power BI, Power Apps, and Power Automate with Microsoft Fabric tools like OneLake and Synapse.
If you’re as excited as I am about the future of data analytics and business intelligence, then I’ll invite you to join our LinkedIn group, Microsoft Fabric & Power Platform, a community dedicated to professionals who are eager to stay ahead of industry trends.
How can a Tableau dashboard that displays contacts (name & company) automatically look up LinkedIn profile URLs?
Of course, researching LinkedIn profiles for a huge list of people is a very repetitive task. So let’s find a solution to improve this workflow…
Step by Step: Integrating Azure Cognitive Services in Tableau
1. Python and TabPy
We use Python to build API requests, communicate with Azure Cognitive Services and to verify the returned search results. In order to use Python within Tableau, we need to setup TabPy. If you haven’t done this yet: checkout my TabPy tutorial.
2. Microsoft Azure Cognitive Services
One of many APIs provided by Azure Cognitive Services is the Web Search API. We use this API to search for name + company + „linkedin“. The first three results are then validated by our Python script. One of the results should contain the corresponding LinkedIn profile.
3. Calculated Field in Tableau
Let’s wrap our Python script together and create a Calculated Field in Tableau:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Adding a URL action with our new Calculated Field will do the trick. Now you can click on the LinkedIn icon and a new browser tab (or the LinkedIn app if installed) opens.
This tutorial is just the tip of the iceberg. If you want to dive deeper into the world of data visualization and analytics, don’t forget to order your copy of my new book, Visual Analytics with Tableau (Amazon). This comprehensive guide offers an in-depth exploration of data visualization techniques and best practices.
I’d love to hear your thoughts. Feel free to leave a comment, share this tweet, and follow me on Twitter and LinkedIn for more tips, tricks, and tutorials on Azure Cognitive Services in Tableau and other data analytics topics.
We use cookies to optimize our website and our service.
Functional
Immer aktiv
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
