In this guide, I'll explain what Microsoft Fabric is in more detail, how it works, and walk you through building a project with it. If you already have an understanding of the platform, you can skip to the Microsoft Fabric project.

Here's what you'll learn about in this guide:

• What is Microsoft Fabric?
• Why you should learn about Microsoft Fabric
• Microsoft Fabric architecture and components
• How to get started by building a simple project
• How to create a workspace in Microsoft Fabric
• How to create a Lakehouse in Microsoft Fabric
• How to use Kaggle API data in Microsoft Fabric
• How to use the Data Wrangler in Microsoft Fabric
• How to perform customer segmentation in Microsoft Fabric
• How to visualize your lakehouse data in Power BI

Prerequisites

To follow along, you will need to have a Power BI license. You can get one for free to practice with using the Microsoft 365 Developer Program.

It would be also be helpful if you have knowledge of Microsoft Power BI and Python.

What is Microsoft Fabric?

Microsoft Fabric is an all-in-one analytics software-as-a-service (SaaS) platform for managing all your data analytics needs and workflows. Microsoft built this end-to-end platform to handle data-related data, from your data storage and migration to your real-time data analytics, data science projects, and data engineering workflow.

But how does it work?

This tool brings together various new and preexisting data tools and technologies—Power BI, OneLake, Azure Data Factory, Data Activator, Power Query, Apache Spark, Synapse Data Warehouse, Synapse Data Engineering, Synapse Data Science, Synapse Real-Time Analytics, Azure Machine Learning, and various connectors.

Why You Should Learn About Microsoft Fabric

The best part of Microsoft Fabric is its simplicity in terms of functionality. Using various technologies together, you can do everything all in one place and focus more on what you can do with it and less on licensing, supporting systems, dependencies, and how to integrate with all these different platforms.

Another benefit of the platform is how it handles your data. This provides and allows you to maintain a single reliable source of information. With Microsoft Fabric’s OneLake, you can have a single, unified data storage.

Microsoft Fabric also has Azure’s OpenAI service integrated into its layer. This way, you can use AI (Co-pilot) to help you discover insights quickly.

Lastly, since it is an all-in-one platform, there is a cost-saving edge since there is no need to subscribe to multiple vendors.

Microsoft Fabric Architecture

Think of Microsoft Fabric as your data estate.

Just like every piece of real estate, Microsoft Fabric has various components in its architecture.

Let’s start by looking at the terminology you'll encounter and need to understand when using Microsoft Fabric's architecture:

Experiences and Workloads:

These refer to the various capabilities of the platform. Every experience on the platform is tailored with a specific user in mind.

Below are some examples of the various experiences/workloads available. You'll notice that each of them are built for a specific purpose, task, and user.

• Data factory: This application gives users over 150 connectors to Lakehouses, warehouses, cloud, and on-premise data sources and orchestrates data pipelines for data transformation. A Lakehouse here refers to a data platform for storing structured and unstructured data. You can also copy your on-prem data to the cloud and load it into OneLake through the Data Factory.
• Synapse data engineering is part of the data engineering experience on the platform. It has some cool features like Lakehouses, built data pipelines, and a Spark engine.
• Synapse data warehouse provides you with a unified and serverless SQL engine. Like your “traditional” data warehouse, you have the full capabilities of your transactional T-SQL features.
• Synapse real-time analytics allows you to stream data from Internet of Things (IoT) devices, telemetry, and logs. You can also use the workload here to analyze semi-structured data using its Kusto Query Language (KQL) capabilities, just like Azure Data Explorer.
• Synapse data science allows you to build, collaborate, train, and deploy fully scalable end-to-end Machine learning (ML) and AI models. You can also carry out your ML experiments in your notebooks and log your models using the Fabric Auto Logging feature. A must-mention tool in this experience is the Data Wrangler, a Fabric graphical user interface for data transformation. With this tool, you can clean your data by simplifying by clicking buttons while the tool automatically generates the Python code for you. It is similar to Power Query.
• Business Intelligence with Power BI helps you quickly turn your business data into insightful analytic reports and dashboards.
• Data Activator allows you to take care of your data observability and monitor workloads in a non-code/low-code way. This tells you when specific data points hit a threshold or match a pattern. You can also automate particular actions and kickoff Power Automates flows when specific conditions occur.
• Copilot in Fabric provides you with an Azure OpenAI Service. This means you can build reports, describe how you want to ingest your data, summarize, explore, and transform your data using the natural language capability of Azure OpenAI.

Workspaces

Workspaces are similar to Power BI’s workspace. Here, you can share and collaborate with others and create reports, Warehouses, Lakehouses, dashboards, and notebooks.

Capacity Unit (CU)

A CU is the ability of your resource to perform or produce an output.

Now we'll look at the various components of Microsoft Fabric's architecture.

OneLake

OneLake is the central data repository for Microsoft Fabric that stores the data in Delta Lake format. Think of it as OneDrive for your data. This repository allows you to explore and find data assets in your organization.

One exciting thing is Shortcuts, which allows you to share or point to data in other locations in OneLake without moving or duplicating the data. This removes any case of data redundancy.

Lakehouses vs Warehouses

While both "houses" hold data, some differences exist between Lakehouses and Warehouses in Microsoft Fabric.

For starters, a Lakehouse can store any data type, whether structured or unstructured. It is, however, stored in the Delta format by default. The Delta format is a storage layer that offers ACID (Atomicity, Consistency, Isolation, Durability) transactions. A Warehouse, on the other hand, is more suited for structured data.

Lakehouses also support Notebooks. So you can work with various languages from PySpark to SQL and R. Warehouses, on the other hand, only use SQL.

Keep in mind, though, that Fabric provides you with two types of Warehouses: SQL Endpoint and Synapse Data Warehouse.

• SQL Endpoint is auto-generated when a Lakehouse is created. This mean you can have a SQL-based experience and can query Lakehouse data using T-SQL language.
• Synapse Data Warehouse is more of your traditional SQL engine. So you can use it to create and query data out of OneLake.

How to Get Started With Microsoft Fabric – An End-to-End Project Example

To get a glimpse of how the Fabric platform works, we will build a little project.

We'll create a Lakehouse to store a mall dataset from Kaggle using the Kaggle API. We will also transform our data using Data Wrangler. Then, we will perform customer segmentation on our data based on the customer's annual income and spending score using the KMeans clustering algorithm. This will allow us to group the customers into various categories like low income earners that don't spend, average income earning customers, and high income customers who do not spend much.

Let's get started.

How to Enable Fabric

The first thing we need to do is to log into Microsoft Power BI. Here, we will activate Microsoft Fabric's capabilities for our workspace.

To do this, follow these steps:

First, navigate to the capacity settings in the admin portal. The admin portal is where administrators control and manage the various Power BI features.

Admin Portal of Microsoft fabric

Then under the Tenant setting tab, look for Microsoft fabric tab.

Under that tab, enable the Users can create fabric items toggle to on. Once you've done that, select Apply.

Now your environment will be set up and the various services should appear at the bottom left of your screen.

Now you can see all the services like Power BI, Data Factory, and so on.

How to Create a Workspace in Microsoft Fabric

We'll use a mall customer segmentation dataset from Kaggle for this demo. This data, as mentioned in Kaggle, was created for the purpose of learning customer segmentation concepts.

Let's talk a little bit about the dataset. Imagine you have a supermarket mall and each customer has a membership card. You also have a data catalog of each customer with basic information like their customer ID, age, gender, annual income and spending score.

Now we want to segment these customer into various groups so we can improve customer loyalty, understand the customers better, and more effectively target our marketing strategy.

To achieve this, we will use the spending score assigned to each customer to define their purchasing power.

To get started, you'll need to create a new workspace. You can do that by following these steps:

1. Head to your Microsoft Fabric home page.
2. Select workspaces and click on New Workspace.
3. Give your workspace a name – I'm calling mine FabricMall.
4. Click on Advanced to view the dropdown options and select Trial if you are making use of your Fabric trial.
5. Click Apply.

How to create a workspace in Microsoft fabric

The next thing you want to do is to create a Lakehouse for your data.

How to Create a Lakehouse in Microsoft Fabric

To create a Lakehouse, first click on New within your workspace. This will display a list of various tasks you can do within your workspace.

Then select More options and select Lakehouse.

Selecting Lakehouse under "More options"

Then give it a name, like FabricMallLake, and click on Open notebook.

Click on New notebook and Open. You can rename your notebook at the top left corner of your notebook. The notebook is similar to the Jupyter notebook experience.

Notebooks in Fabric

How to Use Kaggle API Data in Microsoft Fabric

Notebooks allow us to write, visualize, and execute code. Within the Notebook, we will use Python to perform a customer segmentation on our data in Microsoft Fabric.

First, import Kaggle using the command below:

!pip install Kaggle

Next, you'll need to import your operating system and connect to the Kaggle API.

import os
os.chdir('/lakehouse/default/Files')
os.environ['KAGGLE_USERNAME'] = 'bennyifeanyi'
os.environ['KAGGLE_KEY'] = '050019167fbe0027359cdb4b5eea50fe'
from kaggle.api.kaggle_api_extended import KaggleApi
api = KaggleApi()
api.authenticate()
api.dataset_download_file('vjchoudhary7/customer-segmentation-tutorial-in-python', 'Mall_Customers.csv')

In the code above, os.chdir('/lakehouse/default/Files') represents our File API path. Also remember to replace the username and API Key with your own.

Now import Pandas. This will allow you to read your file.

import pandas as pd
df = pd.read_csv("/lakehouse/default/" + "Files/Mall_Customers.csv")
df.head()

But before we start segmenting our customers, let's transform our data by exploring the data wrangler.

How to Use the Data Wrangler in Microsoft Fabric

One of the most exciting things about this notebook is that you can perform data cleaning tasks without writing code using the Data Wrangler.

To do that, click on Data on the ribbon and select Transform DataFrame in Data Wrangler.

We will perform the following transformations:

• We will convert the gender column to lowercase.
• We will also rename the columns with special characters like the dollar sign, brackets, and a dash. This is because I noticed Fabric finds it hard to handle these characters at the moment.

To do these transformations, follow these steps:

Under the Operation tab, select Convert text to lowercase.

Pick the column – Gender in this example – and select Apply. This will convert your Gender column to lowercase and automatically generate the codes.

Data wrangler: Formatting text

Similarly, under the schema tab, select rename columns.

Rename Annual Income (k$) to AnnualIncome, and Spending Score (1-100) to SpendingScore.

Once you’re done with the transformation, click Add code to notebook.

Data wrangler: Rename column

Back in the notebook, we can visualize our data using the code below:

sparkdf = spark.createDataFrame(df_clean)
display(sparkdf)

Within the chart element created, select Customize chart. Pick the columns you want and select Apply.

Charts in Data Wrangler

Once that's done, we can save the data in the Lakehouse using this code below:

sparkdf.write.format("delta").mode("overwrite").saveAsTable("malldatadf")

Saving data in Lakehouse

How to Perform Customer Segmentation in Microsoft Fabric

For our customer segmentation, we will use the KMeans clustering algorithm to segment the customers based on their annual income and spending score.

K-means clustering is an unsupervised machine learning algorithm. It groups similar data points in your data based on underlying observations, similarities, and input vectors.

We will do this by importing our libraries, applying our K-means by training the K-Means clustering model, and visualizing the clusters of customers based on their annual income and spending score.

We will also include and show the centroids of each cluster, providing insights into the distribution of customers in the dataset.

The centroids here refers to the center points of the clusters found by our algorithm. This is calculated as the average of all the data points in that cluster. When we visualize the clusters, the centroid will be represented with a distinct symbol or color.

Run this code to achieve this:

import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from sklearn.preprocessing import MinMaxScaler
X = df_clean[['AnnualIncome', 'SpendingScore']]
# Feature normalization
scaler = MinMaxScaler()
X_scaled = scaler.fit_transform(X)
kmeans = KMeans(n_clusters=5, init='k-means++', random_state=42)
kmeans.fit(X_scaled)
plt.figure(figsize=(10, 8))
for cluster_label in range(5):  # Loop through each cluster label
cluster_points = X[kmeans.labels_ == cluster_label]
centroid = cluster_points.mean(axis=0)  # Calculate the centroid as the mean position of the data points
plt.scatter(cluster_points['AnnualIncome'], cluster_points['SpendingScore'],
s=50, label=f'Cluster {cluster_label + 1}')  # Plot points for the current cluster
plt.scatter(centroid[0], centroid[1], s=300, c='black', marker='*', label=f'Centroid {cluster_label + 1}')  # Plot the centroid
plt.title('Clusters of Customers')
plt.xlabel('Annual Income (k$)')
plt.ylabel('Spending Score (1-100)')
plt.legend()
plt.show()

Here's the output:

Performing Customer Segmentation in Microsoft Fabric

The result of our analysis shows that our customers can be grouped into 5 clusters:

• Cluster 1 (Purple) are low income earners with a low spending score.
• Cluster 2 (Blue) are low income earners with a high spending score.
• Cluster 3 (Red) are average income earning customers with significant spending scores.
• Cluster 4 (Orange) are high income customers who do not spend much at the mall. They’re probably not satisfied with the services rendered.
• Cluster 5 (Green) are high income customers with a high spending score.

We can also save our prediction as a new dataset using this code:

# Create a new DataFrame to store the clustering results
cluster_df = pd.DataFrame(data=X, columns=['AnnualIncome', 'SpendingScore'])
cluster_df['Cluster'] = cluster_label
sparkclusterdf = spark.createDataFrame(cluster_df)
sparkclusterdf.write.format("delta").mode("overwrite").saveAsTable("clusterdatadf")

Customer segementation prediction

Want to take a look at the notebook? You can download it from my GitHub.

How to Visualize Lakehouse Data in Power BI

Now we can decide to visualize our data on a dashboard within Fabric.

Head back to the FabricMall workspace and select the semantic model type of the FabricMallLake Lakehouse.

semantic model type of the FabricMallLake LakeHouse

Then select Manage default semantic model.

Manage default semantic model In Microsoft Fabric

Pick your dataset, click Confirm, and then select New Report.

Let's visualize the average age in our data. To do this, click on the card visual and drag the age into this card. This will automatically create a visual showing the average age in your dataset.

Power BI service in Microsoft Fabric

Just like in Power BI Desktop, you can create your measure, build your report, and publish your dashboard. You can learn more about how to create visuals in Power BI using this free freeCodeCamp YouTube data analysis video.

Alternatively, you can open Power BI Desktop, and connect to your Lakehouses from Onelake data hub.

Connect to your Lakehouse in Power BI

Where Can I Learn More about Microsoft Fabric?

Though Microsoft Fabric is a pretty new data platform, I hope you can tell that this tool will help you ease the way you and your team consume, analyze, and get insight from your data.

To learn more you can start with the fabric official documentation or any helpful YouTube tutorial like Francis’s Fabric course. I would also advise you to start with freeCodeCamp's Fabric publication tags if you want a compilation of resources.

Lastly, if you’re new to data analysis, start your journey today with freeCodeCamp’s Data Analyst Bootcamp for Beginners on YouTube. It covers everything from SQL, Tableau, Power BI, and Python to Excel, Pandas, and real-life projects building.

If you enjoyed reading this article and/or have any questions and want to connect, you can find me on LinkedIn, Twitter and do check out my articles on freeCodeCamp.

Microsoft Learn Student Ambassadors are student leaders who collaborate with Microsoft to help fellow students learn about technology and engage with various Microsoft technologies and services.

It is important to note that Microsoft Learn Student Ambassadors are not employees of Microsoft. While they work closely with Microsoft and have access to certain resources, training, and support from the company, they do not receive regular employee compensation.

Instead, student ambassadors often receive benefits such as networking opportunities, skill development, and recognition within the tech community.

Responsibilities of Microsoft Learn Student Ambassadors

At the very high level, the responsibilities of student ambassadors include encouraging their peers to use Microsoft Learn, Microsoft's educational platform. It offers free resources, tutorials, and courses on various Microsoft technologies in a bid to help students build skills that open doors.

Student Ambassadors also represent Microsoft technologies on their campuses. In this capacity, Ambassadors engage with faculty and students to showcase the benefits of these tools for education and innovation.

Student Ambassadors are required to complete a variety of pre-determined activities in order to advance through the well-defined program milestones. As you complete these program milestones you can unlock additional program benefits.

You go through these milestones in the following order: new to alpha, alpha to beta, and beta to gold. With each milestone achievement, program certificates are updated.

The different milestones

To attain the Alpha milestone, new student ambassadors are required to complete a learn path on Microsoft Learn within four months of joining the program.

To reach the Beta milestone, first you need to complete the program's technical onboarding as a alpha student ambassador. Then you must host and report an approved event or actively participate in a Student Ambassadors project within twelve months of joining the program.

Finally, to achieve the highest milestone in the program (Gold), you are required to go above and beyond program expectations by demonstrating significant reach and impact.

How to Apply to Be a Microsoft Learn Student Ambassador

Before applying, it is important to note the following:

• The minimum age requirement for applicants is 16 years.
• Full-time enrollment in an accredited academic institution (such as a College or University) is required for applicants.
• Applications are restricted to individual persons and not open to corporate entities.
• Individuals who are Microsoft employees or current contractors are ineligible to apply.

If you meet these qualifications, you can apply to become a Student Ambassador.

The most important thing to know about the application process for the Microsoft Learn Student Ambassador program is that the application is open all year long. But applications are only reviewed (and applicants are gotten back to) quarterly – in January, April, July and October.

You can find the application form here. The application consists of four major parts – privacy and terms, personal information, academic institution, and application questions. Let's go through each one now.

Privacy and terms

In the privacy and terms section, you are required to agree to the Microsoft Privacy Statement and confirm your eligibility for the program.

By completing this section, you accept that as you submit your application, your information will be shared with the Microsoft program lead who manages Microsoft Learn Student Ambassadors selection in your region.

Also in this section, you are informed that if your application is not accepted, although all the information you provided will be removed from their servers within twelve months of the application submission date. You can request removal of your application data at any time without constraints.

Personal information

In the personal information section, the required prompts include your name, contact email address, date of birth, gender, pronoun and country or region.

You can also enter your preferred programming language in this section (but this particular question is not required).

In this section, you can also add the social media profiles you use for community leadership as an allusion to your online influence and network.

Academic institution

In the academic institution section, as the name implies, the prompts are aimed at getting the country and region of your academic institution, its name, your degree program and your expected graduation date.

The available options include Computer Science, Electrical Engineering, Software Engineering, Systems Engineering, Engineering - Other, Information Systems, Cyber Security, Business and Others). You'll also be asked to list your degree level (the selections accessible are Bachelor, Master and PhD).

Application questions

In the application questions section, as an applicant, you are required to answer the following three questions.

• Tell us about one specific technical skill you have learned, and how you were guided to learning it. How would you use that experience to be a better Student Ambassador? (Guide)
• Tell us about a specific time you welcomed a new person into one of your communities. How would you use that experience to be a better Student Ambassador? (Welcome)
• Tell us about a specific event you have planned that connected diverse audiences together. How would you use that experience to be a better Student Ambassador? (Connect)

Now, here's the catch. Whilst every applicant gets to answer all three questions, each applicant also gets the opportunity to answer any two of their choice with written responses and one as a video response. I think this is an attempt to get applicants to demonstrate their public speaking skills.

The video must be under two minutes and should be recorded in a quiet space without background noise. The written responses, on the other hand, must be a minimum of 100 characters and maximum of 2000 characters each.

Also, the video response's link has to be publicly accessible to anyone with the link. If an applicant chooses to record their video in any language that is not English, they must include English subtitles.

There's an option to pick from a list of technologies and highlight which ones interest you.

Finally, in more than 100 characters and less than 1000 characters, you can share the link to your Microsoft Learn profile (if you have one) and provide the reviewing team with details about your engagements within your community, both in online and on-campus settings.

In that space, you can also let them know about any organizations you are part of, particularly if you hold leadership roles. Feel free to share any additional information you consider important in this section.

The list of technologies to choose from

The application process/essays may seem quite lengthy. But the good thing about it is that the essay portal automatically saves your application as you go, so you can leave at any time and come back later to continue your application.

Benefits of Being a Microsoft Learn Student Ambassador

The benefits of being a Microsoft Learn Student Ambassador are many. First, you benefit from comprehensive technical and leadership training and gain access to Microsoft's extensive array of learning materials. You also get to connect with a worldwide community of students who share your enthusiasm for building the future you envision.

In my opinion, the biggest thing student ambassadors stand to gain is the community they become a part of alongside fellow ambassadors. This community offers an extensive network of peers, mentors, and professionals who are all invested in the same mission. It becomes a platform for collaboration, knowledge-sharing, and personal growth.

Here are a few other benefits that are accessible as you progress through the milestones:

Benefits of the New Milestone

Microsoft 365: This is a product family of productivity software, collaboration and cloud-based services owned by Microsoft. As a student ambassador in the new milestone, you get Microsoft 365 subscriptions.

The Microsoft 365 subscriptions include full Office desktop apps such as Word, Excel, Outlook, PowerPoint, Access and Publisher for Windows PCs, as well as access to additional OneNote features.

Benefits of the Alpha Milestone

In addition to the benefits that comes with the new milestone, student ambassadors get access to Visual Studio Enterprise. This annual subscription provides Student Ambassadors with $150 monthly Azure credit, license keys to various Microsoft Products, and many other benefits.

In addition, in line with the program's dedication to the growth of student ambassadors, student ambassadors in the Alpha milestone and beyond get free access to LinkedIn Learning.

Benefits of the Beta Milestone

In addition to the existing benefits, upon reaching this milestone, student ambassadors get a Beta swag box.

The Microsoft Learn Student Ambassador Beta Swag Box

Benefits of the Gold Milestone

On top of the previous benefits, student ambassadors who achieve this milestone get a Gold swag box, more program leadership opportunities, consideration for special events and activities, and consideration for MVP mentorship and nomination. This is the highest milestone in the program.

The Microsoft Learn Student Ambassador Gold Swag Box

Windows Insider for Student Ambassadors

Finally, one last benefit I want to touch on is the Windows Insider Program for Student Ambassadors.

Thanks to the Windows Insider Program's collaboration with Microsoft Learn Student Ambassadors, when Student Ambassadors become part of the Windows Insider Program, they will get a chance to preview upcoming Windows features and offer input to influence the direction Windows takes in the future.

Wrapping Up

Once you graduate from the Microsoft Learn Student Ambassador program at the Gold milestone, you will be eligible for nomination to the Windows Insider Most Valuable Professional (MVP) program.

This prestigious status comes with exclusive interaction opportunities with the Windows engineering team, special event invitations, and complimentary subscriptions to tools such as Visual Studio Enterprise, Office 365, and LinkedIn Premium. You you will receive a personalized award kit to commemorate this achievement.

I hope this guide helps you apply for the program and know what to expect! Thanks for reading.

You can learn everything you need to know to earn the Microsoft Power Platform Fundamentals Certification by completing this free 4-hour course that we just posted on the freeCodeCamp.org YouTube channel.

Andrew Brown created this course. He is the founder of ExamPro and has created a ton of popular courses about how to prepare for different technical certifications. He has helped thousands of people pass certifications.

What is the Microsoft Power Platform Fundamentals Certification?

The Microsoft Power Platform Fundamentals certification is a credential that certifies your understanding of the core capabilities of the Microsoft Power Platform. It's aimed at developers beginning to work with Microsoft's suite of business analytics tools, and it verifies their ability to utilize these tools to drive business improvements.

The certification covers the following primary areas:

Understanding the business value of Power Platform: This involves understanding the value of Power Platform services, and their role in the Microsoft 365 ecosystem. It covers Power Apps, Power Automate, Power BI, and Power Virtual Agents.

Understanding the core components of Power Platform: This includes understanding the function and capabilities of Power BI, Power Apps, Power Automate, and Power Virtual Agents. Also understanding Power Platform's extensibility and integration with other services and tools.

Demonstrating the business value of Power BI: Candidates need to be able to demonstrate how Power BI can bring data to life, how Power BI can provide data insights, and how Power BI can help organizations make informed business decisions.

Demonstrating the business value of Power Apps: This involves understanding how Power Apps can simplify app development, and how it can help organizations create custom solutions.

Demonstrating the business value of Power Automate: Candidates must demonstrate how Power Automate can boost productivity, reduce manual tasks, and automate business processes.

Demonstrating the business value of Power Virtual Agents: This includes understanding how Power Virtual Agents can enhance customer service, streamline common actions, and improve overall customer engagement.

Who is the Microsoft 365 Fundamentals Certification for?

The Microsoft Power Platform Fundamentals certification is intended for people looking to demonstrate their knowledge and skills in using Microsoft Power Platform. This includes:

Beginners in IT: The certification is designed to be an entry-level credential, suitable for those with little to no prior experience in IT or Microsoft Power Platform. This makes it an ideal starting point if you're just beginning your IT career.

Non-Technical Professionals: This includes business analysts, data enthusiasts, and others who need to work with data, improve productivity, automate business processes, or create custom business applications but don't necessarily have a technical or programming background. The Power Platform offers a low-code or no-code approach that allows non-technical users to work effectively.

Technical Professionals: Even if you're a developer, data professional, or IT professional, this certification can be valuable. It provides recognition for your ability to work with the Power Platform and can help broaden your skillset, making you more valuable to your organization.

Students or Job Seekers: For those looking to break into the field of data analysis, business analysis, or IT in general, this certification can be a valuable addition to your resume, making you more attractive to potential employers.

Remember, although this is a fundamental certification, having a basic understanding of data processing and business value along with familiarity with the internet, cloud computing, and software as a service (SaaS) environments can be beneficial.

What are the topics covered in this course?

This video course covers all the topics you need to know to get certified. Here is what is covered:

☁️ Introduction

• Exam Breakdown
• Practice Exam Sample

☁️ Business value of Microsoft Power Platform services

• What is Power Platform
• Power Platform Features
• Business value of Power Platform

☁️ Microsoft Power Platform value in extending business solutions

• Power Platform apps with Dynamics 365 apps
• Microsoft Power Platform with Microsoft 365 apps and services
• How Power Platform apps work together
• Power Platform solutions with Microsoft Teams
• Power Platform using Microsoft Azure services

☁️ Microsoft Power Platform administration and security

• Power Platform Security Model
• Environments in Power Platform
• Environments Permissions
• Power Platform admin center
• Microsoft 365 admin center

☁️ Microsoft Dataverse

• Overview of Dataverse
• Traditional Databases vs Microsoft Dataverse
• Tables and columns in Dataverse
• Relationships
• Environments in Dataverse
• Business Rules
• Dataflows in Dataverse
• Dataverse Connectors
• Standard and Premium Connectors
• Custom Connectors
• Triggers and Actions

☁️ Capabilities of Power BI

• Power BI
• Parts of Power BI
• Power BI Workspace
• Datasets
• Reports
• Dashboards
• Dashboards vs Reports
• Template Apps

☁️ Connect to and consume data

• Data modeling and visualization
• Types of visualization in Power BI
• Custom Visuals
• Filter data with Power BI and Slicers
• Buttons in Power BI
• Power Query Editor
• Aggregate
• Power BI Security and Administration

☁️ Build a basic dashboard using Power BI

• Create an Account
• Power BI Sample Report
• Create a Report and Dashboard

☁️ Capabilities of Power Apps

• Power Apps
• Power Apps Portals
• AI Builder
• AI Builder in Power Apps
• Functions in Power Apps

☁️ Canvas apps

• Canvas Apps
• Canvas Apps Basic Elements
• Canvas Apps Demo

☁️ Model driven apps

• Model driven apps
• Model driven apps and canvas apps differences
• Model driven app design phase
• Building blocks on model driven apps
• Business Logic
• Dashboards in Power Apps
• Model Driven App Template
• Create a Model Driven App

☁️ Capabilities of Power Automate

• Power Automate
• Types of flows in Power Automate
• Cloud flow templates
• Actions and Triggers
• Loops
• Switch
• Conditions
• Expressions
• Approvals
• Use cases for Power Automate
• Power Automate Apps
• Actions for Power Automate desktop flow
• Document ProcessingAI Builder in Power Automate

☁️ Build a basic cloud flow

• Create an automated cloud flow

☁️ Complementary Microsoft Power Platform solutions

• Power Virtual Agents
• Power Virtual Agents Feature
• Topics
• Entities and Actions
• Publishing
• Add chatbot to Teams
• Create a Topic
• Power Pages
• Power Pages capabilities
• Power Pages templates
• Integration with other Power Platform component

Head on over to freeCodeCamp's YouTube channel to start working through the full 4 hour course.

A common functionality that many work apps have is letting users schedule a meeting with others in their organization. Here's how to do it on Microsoft 365.

Work Apps Need Work Data

If your organization uses Microsoft 365, it should consider integrating it with apps that it uses for work.

Bringing data and insights from Microsoft 365 into the context of work apps helps users stay in the flow of their work, and have access to all relevant information in one place.

Say, your organization has an app that it uses for managing projects. Along with the information about the project itself, users of the app will also need information about the people on the project to get updates or schedule meetings.

Information about people in your organization and their work is stored on Microsoft 365 and you can bring it into the context of your app.

When you build apps for work, you can use Microsoft Graph - the API for Microsoft 365, to interact with Microsoft 365 and retrieve the data that's stored there.

Find a Meeting Time and Schedule a Meeting with Attendees on Microsoft 365

Many work scenarios require the ability to schedule a meeting with others in your organization. For organizations that use Microsoft 365, this functionality is readily available in Microsoft Outlook.

Scheduling a meeting in Microsoft Outlook on the web

But what if you don't want your users to leave your app, go to Outlook, and manually schedule the meeting with the right people?

Using Microsoft Graph you can let users select attendees, find a suitable meeting time, and schedule a meeting directly from your app. Let me show you how.

Custom web app allowing users to find a meeting time and schedule a meeting

A sample app built using code fragments in this article is available on GitHub. To run it, you'll need Node.js LTS and a Microsoft 365 developer tenant which you can get for free from the Microsoft 365 developer program.

Select meeting attendees

The first step is to let users select with whom they'd like to meet.

Web app with a few people selected to find available meeting times

The easiest way to do this is by using the People picker component from Microsoft Graph Toolkit.

Microsoft Graph Toolkit is a set of web components connected to Microsoft Graph, that work in any web framework.

To add the people picker, you'd add to your app:

<mgt-people-picker></mgt-people-picker>

People picker automatically retrieves information about people in your organization from Microsoft 365 and filters the list as you type. Each person shows up with their name and picture to help users select the right person.

Selecting people from Microsoft 365 in a custom web app

Combining it with the Login component from Microsoft Graph Toolkit, you let users sign in to your app with their Microsoft 365 account and get access to the Microsoft Graph API.

Find meeting times

The next step is to find available meeting times for the selected attendees including the current user.

Available meeting times for the selected people from Microsoft 365

Rather than having to download calendars for all users and manually look for a suitable meeting slot, you can call the findMeetingTimes Microsoft Graph API, passing as arguments the array of the attendees and the meeting duration.

const meetingTimes = await graphClient
  .api('/me/findMeetingTimes')
  .post({
    attendees: document.querySelector('mgt-people-picker').selectedPeople.map(p => {
      return {
        emailAddress: {
          address: p.userPrincipalName,
          name: p.displayName
        },
        type: 'required'
      };
    }),
    maxCandidates: 3,
    meetingDuration: `PT${document.querySelector('#duration').value}`,
    returnSuggestionReasons: true,
    minimumAttendeePercentage: 100
  });
availableMeetingTimes = meetingTimes.meetingTimeSuggestions;

You can get the list of attendees from the People picker which exposes the list of people selected by the user.

When requesting available meeting times, you can pass many additional options, such as how many suggestions you'd like to get (maxCandidates), how many attendees have to be able to attend the meeting at minimum (minimumAttendeePercentage), or between which times the meeting should take place.

Finding available meeting times for the selected attendees and meeting duration

Schedule a meeting

The final step is to schedule a meeting.

Scheduling a meeting on Microsoft 365 from a custom web app

At this point, you have all the necessary information to send a meeting invite to the selected people on behalf of the current user.

You can do this by calling the events Microsoft Graph API, passing in the request body all information such as subject, start- and end-time, and the list of attendees.

const meetingTime = availableMeetingTimes[selectedMeetingTime].meetingTimeSlot;

await graphClient
  .api('/me/events')
  .post({
    subject: document.querySelector('#subject').value,
    start: meetingTime.start,
    end: meetingTime.end,
    attendees: document.querySelector('mgt-people-picker').selectedPeople.map(p => {
      return {
        emailAddress: {
          address: p.userPrincipalName,
          name: p.displayName
        },
        type: 'required'
      };
    })
  });

Scheduling a meeting on Microsoft 365 from a custom web app

Summary

By integrating Microsoft 365 into your work apps, you help your users work more effectively.

Using the Microsoft Graph API, you can bring data and insights from Microsoft 365 into your work apps. This provides your users with all the information they need to complete their tasks.

Because they have all information they need at their fingertips, they don't need to switch between different apps and can stay in the flow of their work. And by using web components from the Microsoft Graph Toolkit, you can build apps connected to Microsoft 365 even quicker.

Check out the sample app, and I'm looking forward to hearing from you how about you like it and what other integration scenarios you'd be interested in.

If you're a web developer and you work with an organization that uses Microsoft 365, you probably use it to manage your meetings.

In this tutorial, you'll learn how you can build a simple personal assistant in under 10 minutes that'll show a Microsoft 365 user the meetings they have left for the day.

How to Build Apps for Microsoft 365

Previously I showed you how you can use your existing web development skills to tap into data and insights stored on Microsoft 365 and build apps for your organization.

In just 10 minutes, you can build a simple app that shows you the profile of the user signed in to your app with their Microsoft 365 account.

Simple Microsoft 365 application showing the profile of the current user

This time, I want to show you how you can build – in just 10 minutes – a simple personal assistant that shows upcoming meetings for the currently signed-in user.

Custom app for Microsoft 365 showing list of upcoming meetings

What you'll need

• Node.js LTS (at the time of writing this article, it's 16.18.0)
• a Microsoft 365 developer tenant. You can get one for free from Microsoft 365 developer program, and it'll give you access to all Microsoft 365 APIs you need to build your apps, along with some demo data to get started
• 10 minutes of your time

How to Register Your Application on the Microsoft Cloud

Create a folder for your application, where you'll store all app files. Open a terminal and change the working directory to that folder.

In the terminal, run the following command:

npx -p @pnp/cli-microsoft365 -- m365 login --authType browser

In your web browser, sign in with your newly created Microsoft 365 developer account:

Sign into your Microsoft 365 developer account

Next, back in the terminal, run the following command:

appId=$(npx -p @pnp/cli-microsoft365 -- m365 aad app add --name "Upcoming Meetings" --multitenant --redirectUris "http://localhost,http://localhost/index.html" --platform spa  --query "appId" -o text)

With these two commands, you've used the CLI for Microsoft 365 to sign in to Microsoft 365 and register your new app on the Microsoft Cloud.

Every app that integrates with Microsoft 365 must be registered and provide information such as the application's name and type (platform). For single-page apps, you also need to specify the application's URL, which is used to ensure that users are signing in to the right app.

Next, write the ID of the newly created app to a file that you'll reference in your app. In the terminal, run:

echo "const appId = '$appId';" > env.js

How to Create your App

Open your app folder in your code editor. Create a new file named index.html and paste the following code:

<html>
<head>
  <title>Upcoming meetings</title>
  <!-- TODO #1: add libraries -->
</head>
<body>
  <h1>Upcoming meetings</h1>
  <div id="auth"></div>
  <div id="upcomingMeetings"></div>
  <script>
    // TODO #2: add app code
  </script>
</body>
</html>

This code is a simple HTML page with two placeholders: one for the libraries that you'll use to build your app, and another for the app's code. It also contains a div where you'll show the login/logout button and another div where you'll show the list of upcoming meetings for today.

How to Add Libraries

Replace the TODO #1 comment with the following code:

  <script src="https://alcdn.msauth.net/browser/2.28.3/js/msal-browser.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/@microsoft/microsoft-graph-client/lib/graph-js-sdk.js"></script>
  <script
    src="https://cdn.jsdelivr.net/npm/@microsoft/microsoft-graph-client/lib/graph-client-msalBrowserAuthProvider.js"></script>
  <script src="./env.js"></script>

To build this app you'll use a few libraries:

• MSAL.js which helps you handle signing users in with their Microsoft 365 account
• Microsoft Graph JavaScript SDK which simplifies calling Microsoft Graph – the API to access data and insights on Microsoft 365
• MSAL Browser Auth Provider which integrates MSAL.js with the Microsoft Graph JS SDK
• the previously created env.js file with the ID of your app

Using these libraries will help you build your app faster, and you won't have to worry about the details of how to sign users in, get an access token, or properly handle API errors.

How to Sign Users In with Their Microsoft 365 Account

Replace the TODO #2 comment with the following code:

    (() => {
      // TODO #3: create MSAL client

      // TODO #5: handle login/logout

      // TODO #6: create Microsoft Graph client

      // TODO #7: get upcoming meetings from Microsoft 365 user's calendar

      function render() {
        // TODO #4: render UI
      }

      render();
    })();

This code is an immediately invoked function expression (IIFE) that encapsulates the app's code and runs when the app loads. It also contains several placeholders for the code that you'll write in the next steps.

Replace the TODO #3 comment with the following code:

      const scopes = ['Calendars.Read'];
      const msalConfig = {
        auth: {
          clientId: appId
        }
      };
      const msalClient = new msal.PublicClientApplication(msalConfig);

In this fragment, you're creating a new configuration object for the MSAL.js library which includes a reference to the ID of the app you created earlier. You also define a list of permissions (also known as scopes) that your app will have to request to be able to access information from the calendar of the currently signed-in user.

Next, you pass this object to the PublicClientApplication constructor to create a new instance of the MSAL client. You'll use this to sign users into your app with their Microsoft 365 account.

Right now, the app shows an empty page. Let's change that by replacing the TODO #4 comment with the following code:

        msalClient
          .handleRedirectPromise()
          .then(response => {
            const accounts = msalClient.getAllAccounts();

            if (accounts.length === 0) {
              document.querySelector('#auth').innerHTML = '<button>Login</button>';
              document.querySelector('#auth button').addEventListener('click', login);
              document.querySelector('#upcomingMeetings').innerHTML = '';
            }
            else {
              document.querySelector('#auth').innerHTML = '<button>Logout</button>';
              document.querySelector('#auth button').addEventListener('click', logout);
              // TODO #8: load upcoming meetings
            }
          });

When signing users in with their Microsoft 365 account, you'll redirect them to the Microsoft 365 sign-in page. When they sign in, they'll be redirected back to your app.

The handleRedirectPromise function will handle processing the information that Microsoft 365 sends to your app. When users come to your app and haven't signed in yet, the handleRedirectPromise function will resolve with a null response.

After handling the redirect, you check using MSAL if there are any users signed in to your app. If there are none (accounts.length === 0 ), you show the login button. If there are users signed in, you show the logout button. Later, you'll add the code to show upcoming meetings from the user's calendar.

Both the login and the logout button are missing their click handlers, so let's add them by replacing TODO #5 with the following code:

      function login(e) {
        e.preventDefault();
        msalClient.loginRedirect({
          scopes
        });
      }

      function logout(e) {
        e.preventDefault();
        msalClient.logoutRedirect();
      }

In both cases, you're using MSAL to sign users in and out by redirecting them to the Microsoft 365 login/logout page. In the login function, you also pass the same set of permissions so that users will be prompted only once to approve the same set of permissions when signing in to the app and loading its data.

At this stage, your app should let you sign in and out using your Microsoft 365 account. To verify that everything is working as intended, save your changes, and in the terminal run:

npx lite-server

In your web browser, navigate to http://localhost:3000 and sign in to your app. You should see the following screen:

After signing in to your app

When you click the login button, you will be asked to sign in with your Microsoft 365 account. Then, when you click the logout button, you will be signed out from Microsoft 365 and the app.

This concludes the first part of building the app, and you're ready to start retrieving data from Microsoft 365 using Microsoft Graph.

How to Show Upcoming Meetings from Microsoft 365

Now that your app supports signing in and out with Microsoft 365 accounts, the next step is to add the code to retrieve information about upcoming meetings from the signed-in user's calendar.

How to Get a Microsoft Graph Client

Replace the TODO #6 comment with the following code:

      function getGraphClient(account) {
        const authProvider = new MSGraphAuthCodeMSALBrowserAuthProvider.AuthCodeMSALBrowserAuthenticationProvider(msalClient, {
          account,
          scopes,
          interactionType: msal.InteractionType.Redirect,
        });

        return MicrosoftGraph.Client.initWithMiddleware({ authProvider });
      }

This function takes as an argument the Microsoft 365 account that has been used to sign in to the app. It uses it to create a Microsoft Graph client that you'll use to call Microsoft Graph APIs and get data from Microsoft 365.

Also note that you're passing the same list of API permissions (scopes) that you defined earlier. This will let the graphClient get an access token for the Microsoft Graph API with access to calendar information.

How to Load Information about Upcoming Meetings

Next, let's replace the TODO #7 comment with the following code:

      function getTimeString(dateFromGraph) {
        const date = new Date(dateFromGraph + 'Z');
        const minutes = date.getMinutes();
        return `${date.getHours()}:${minutes < 10 ? '0' : ''}${minutes}`;
      }

      function loadUpcomingMeetings(graphClient) {
        // configure Microsoft Graph query to retrieve upcoming meetings for today
        const now = new Date();
        const midnight = new Date();
        midnight.setDate(midnight.getDate() + 1);
        midnight.setHours(0);
        midnight.setMinutes(0);
        midnight.setSeconds(0);
        midnight.setMilliseconds(0);

        graphClient
          .api(`/me/calendarview?startdatetime=${now.toISOString()}&enddatetime=${midnight.toISOString()}&$orderby=start/dateTime`)
          .get()
          .then(res => {
            if (res.value.length === 0) {
              document.querySelector('#upcomingMeetings').innerHTML = 'No more meetings for today';
            }
            else {
              const meetingsHtml = res.value.map(meeting => {
                return `<dt>
                  ${getTimeString(meeting.start.dateTime)}-${getTimeString(meeting.end.dateTime)} ${meeting.subject}
                </dt>
                <dd>
                  ${meeting.location.displayName} with ${meeting.attendees.map(attendee => attendee.emailAddress.name).join(', ')}
                </dd>`;
              });
              document.querySelector('#upcomingMeetings').innerHTML = `<dl>${meetingsHtml.join('')}</dl>`;
            }
          });
      }

The loadUpcomingMeetings function takes as an argument an instance of the Microsoft Graph client as returned by the Microsoft Graph JavaScript SDK and uses it to call the Microsoft Graph API.

How to Call the Microsoft Graph API

To get the list of upcoming meetings for today, you're calling the /me/calendarview Microsoft Graph API. This endpoint takes as arguments the start and end date and time and returns a view of the calendar.

Using this endpoint allows you to include occurrences of recurring meetings that users might have in their calendars. To retrieve upcoming meetings for today, you take the current time as the start date/time, and the coming midnight as the end date/time.

How to Process the Microsoft Graph API Response

After calling the API, you get back an array with objects that represent upcoming meetings for the current user. Each meeting contains information such as the meeting's subject, start and end time, location, and attendees.

If the returned array is empty, it means that there are no upcoming meetings. If it contains one or more items, you use the map function to turn meeting items into an HTML string with dt and dd, showing the meeting's subject, start and end time in a human-readable format, the meeting's location, and who are the attendees.

How to Format Events' Start and End Time

To format the date, you use the getTimeString function. It takes as an argument the date returned by Microsoft Graph.

The important thing to keep in mind here is that by default, Microsoft Graph API returns times and dates in the UTC timezone. Because the timezone information is stored separately, before parsing the date, you need to add the Z UTC timezone denominator to the date string.

The final piece is to tie it all together and call both functions after the user signs in to the app. Replace the TODO #8 comment with the following code:

              const graphClient = getGraphClient(accounts[0]);
              loadUpcomingMeetings(graphClient);

That's it! When you save your changes and go back to the browser, you'll see it automatically refreshed in the background, and you're prompted to grant the app access to your calendar information.

Setting permissions on the app

After your grant access, by clicking the Accept button, you'll see information about your upcoming meetings displayed on the page.

Information about upcoming meetings retrieved from the Microsoft 365 calendar

Summary

In this article, you learned how to use the Microsoft Graph SDK to connect to the Microsoft Graph API and retrieve information about upcoming meetings from the current user's calendar.

This is just one of many scenarios and types of apps that you can implement on Microsoft 365. For more information about the type of data and insights stored on Microsoft 365 that you can tap into, check out the Microsoft Graph API documentation.

If you're a web developer and work with an organization that uses Microsoft 365, or you build applications that you sell to customers, I've got great news for you: you can use your existing skills to build applications that integrate with Microsoft 365.

What's Microsoft 365?

Microsoft 365 is a set of applications, like Microsoft Teams, Outlook, Word, or SharePoint, that organizations use for work. Every day, millions of people use Microsoft 365 to chat, meet, send emails, create documents, and more.

But Microsoft 365 isn't just a set of apps. It's also a platform that enables developers, like yourself, to build applications. These apps can tap into data and insights stored on Microsoft 365 and bring them into people's workflows.

You Can Build your First Microsoft 365 App in Under 10 minutes

Following is a short tutorial that'll show you how to build a simple single-page app that integrates with Microsoft 365. The app will let users sign in with their Microsoft 365 account, and see their profile information.

Microsoft 365 application

It doesn't seem like much, but while building this simple app, you'll learn the basics of building Microsoft 365 apps.

What you'll need

• Node.js LTS (at the time of writing this article, it's 16.17.1)
• a Microsoft 365 developer tenant. You can get one for free from Microsoft 365 developer program, and it'll give you access to all Microsoft 365 APIs you need to build your apps, along with some demo data to get started
• 10 minutes of your time

Ready? Let's go!

Register your Application on the Microsoft Cloud

Create a folder for your application, where you'll store all app files. Open a terminal and change the working directory to that folder.

In the terminal, run the following command:

npx -p @pnp/cli-microsoft365 -- m365 login --authType browser

In your web browser, sign in with your newly created Microsoft 365 developer account:

Sign into your Microsoft 365 developer account

Next, back in the terminal, run the following command:

appId=$(npx -p @pnp/cli-microsoft365 -- m365 aad app add --name "Hello world" --multitenant --redirectUris "http://localhost,http://localhost/index.html" --platform spa --query "appId" -o text)

With these two commands, you've used the CLI for Microsoft 365 to sign in to Microsoft 365 and register your new app on the Microsoft Cloud.

Every app that integrates with Microsoft 365 must be registered and provide information such as the application's name and type (platform). For single-page apps, you also need to specify the application's URL, which is used to ensure that users are signing in to the right app.

Next, write the ID of the newly created app to a file that you'll reference in your app. In the terminal, run:

echo "const appId = '$appId';" > env.js

Create your App

Open your app folder in your code editor. Create a new file named index.html and paste the following code:

<html>
<head>
  // TODO #1: add libraries
</head>
<body>
  <h1>Hello Microsoft 365</h1>
  <div id="auth"></div>
  <pre></pre>
  <script>
    // TODO #2: add app code
  </script>
</body>
</html>

This code is a simple HTML page with two placeholders: one for the libraries that you'll use to build your app, and another for the app's code. It also contains a div where you'll show the login/logout button and a pre element where you'll show the user's profile information.

Add Libraries

Replace the TODO #1 comment with the following code:

  <script src="https://alcdn.msauth.net/browser/2.28.3/js/msal-browser.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/@microsoft/microsoft-graph-client/lib/graph-js-sdk.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/@microsoft/microsoft-graph-client/lib/graph-client-msalBrowserAuthProvider.js"></script>
  <script src="./env.js"></script>

To build this app you'll use a few libraries:

• MSAL.js which helps you handle signing users in with their Microsoft 365 account
• Microsoft Graph JavaScript SDK which simplifies calling Microsoft Graph – the API to access data and insights on Microsoft 365
• MSAL Browser Auth Provider which integrates MSAL.js with the Microsoft Graph JS SDK
• the previously created env.js file with the ID of your app

Using these libraries will help you build your app faster, and you won't have to worry about the details of how to sign users in, get an access token, or properly handle API errors.

Sign Users In with their Microsoft 365 Account

Replace the TODO #2 comment with the following code:

    (() => {
      // TODO #3: create MSAL client

      // TODO #5: handle login/logout

      // TODO #6: create Microsoft Graph client

      // TODO #7: get Microsoft 365 user profile

      function render() {
        // TODO #4: render UI
      }

      render();
    })();

This code is an immediately invoked function expression (IIFE) that encapsulates the app's code and runs when the app loads. It also contains several placeholders for the code that you'll write in the next steps.

Replace the TODO #3 comment with the following code:

      const msalConfig = {
        auth: {
          clientId: appId
        }
      };
      const msalClient = new msal.PublicClientApplication(msalConfig);

In this fragment, you're creating a new configuration object for the MSAL.js library which includes a reference to the ID of the app you created earlier.

Next, you pass this object to the PublicClientApplication constructor to create a new instance of the MSAL client, which you'll use to sign users into your app with their Microsoft 365 account.

Right now, the app shows an empty page. Let's change that by replacing the TODO #4 comment with the following code:

        msalClient
          .handleRedirectPromise()
          .then(response => {
            const accounts = msalClient.getAllAccounts();

            if (accounts.length === 0) {
              document.querySelector('#auth').innerHTML = '<button>Login</button>';
              document.querySelector('#auth button').addEventListener('click', login);
              document.querySelector('pre').innerHTML = '';
            }
            else {
              document.querySelector('#auth').innerHTML = '<button>Logout</button>';
              document.querySelector('#auth button').addEventListener('click', logout);
              // TODO #8: show user profile
            }
          });

When signing users in with their Microsoft 365 account, you'll redirect them to the Microsoft 365 sign-in page. When they sign in, they'll be redirected back to your app.

The handleRedirectPromise function will handle processing the information that Microsoft 365 sends to your app. When users come to your app and haven't signed in yet, the handleRedirectPromise function will resolve with a null response.

After handling the redirect, you check using MSAL if there are any users signed in to your app. If there are none (accounts.length === 0 ), you show the login button. If there are users signed in, you show the logout button. Later, you'll add the code to show the user's profile information.

Both, the login and the logout button are missing their click handlers, so let's add them by replacing TODO #5 with the following code:

      function login(e) {
        e.preventDefault();
        msalClient.loginRedirect();
      }

      function logout(e) {
        e.preventDefault();
        msalClient.logoutRedirect();
      }

In both cases, you're using MSAL to sign users in and out by redirecting them to the Microsoft 365 login/logout page.

At this stage, your app should let you sign in and out using your Microsoft 365 account. To verify that everything is working as intended, save your changes, and in the terminal run:

npx lite-server

In your web browser, navigate to http://localhost:3000 and sign in to your app. You should see the following screen:

After signing into your app

When you click the login button, you will be asked to sign in with your Microsoft 365 account. Then, when you click the logout button, you will be signed out from Microsoft 365 and the app.

This concludes the first part of building the app, and you're ready to start retrieving data from Microsoft 365 using Microsoft Graph.

Show Microsoft 365 User Profile

Now that your app supports signing in and out with Microsoft 365 accounts, the next step is to add the code to retrieve the user's profile information from Microsoft 365.

Replace the TODO #6 comment with the following code:

      function getGraphClient(account) {
        const authProvider = new MSGraphAuthCodeMSALBrowserAuthProvider.AuthCodeMSALBrowserAuthenticationProvider(msalClient, {
          account,
          scopes: ['user.read'],
          interactionType: msal.InteractionType.Redirect,
        });

        return MicrosoftGraph.Client.initWithMiddleware({ authProvider });
      }

This function takes as an argument the Microsoft 365 account that has been used to sign in to the app and uses it to create a Microsoft Graph client that you'll use to call Microsoft Graph APIs and get data from Microsoft 365.

Next, let's replace the TODO #7 comment with the following code:

      function loadUserProfile(graphClient) {
        graphClient
          .api('/me')
          .get()
          .then(res => {
            document.querySelector('pre').innerHTML = JSON.stringify(res, null, 2);
          });
      }

This function takes as an argument an instance of the Microsoft Graph client as returned by the Microsoft Graph JavaScript SDK and uses it to call the Microsoft Graph API.

In this case, you're calling the /me Microsoft Graph API which returns profile information for the currently signed-in user. The retrieved data is then displayed in the pre element on the page.

The final piece is to tie it all together and call both functions after the user signs in to the app. Replace the TODO #8 comment with the following code:

              const graphClient = getGraphClient(accounts[0]);
              loadUserProfile(graphClient);

That's it! When you save your changes and go back to the browser, you'll see it automatically refreshed in the background, and you're prompted to grant the app access to your profile information.

Setting permissions on the app

After your grant access, by clicking the Accept button, you'll see the profile information displayed in the pre element on the page.

Profile information

Congratulations! You've just built your first app connected to Microsoft 365.

This is just the beginning

You've just built your first app on Microsoft 365: a single-page app connected to Microsoft 365 that shows the Microsoft 365 profile of the current user. This app is a simple example to show you how to get started building apps on Microsoft 365.

There's a lot more data and insights stored in Microsoft 365 that your apps can access, and there are many types of apps that you can build. The best part is that you can use the same techniques that you've learned in the last 10 minutes to build them.

You've just built a Microsoft 365 app using JavaScript, but you can build apps on Microsoft 365 using any programming language. One thing that all Microsoft 365 apps have got in common is that they bring in data and insights from Microsoft 365 helping people work together.

Curious to learn more? See what other developers and Microsoft partners are building on Microsoft 365. And if you want to learn further, check out the Microsoft Graph Fundamentals learning path. And please, don't hesitate to reach out if you have any questions.

Sometimes, when you're trying to open an app or game, you might get the error “the code execution cannot proceed because msvcp140.dll was not found”.

This error can come in another form, too – "The program can’t start because MSVCP140.dll was not found. Try reinstalling the program to fix this problem".

This could happen because the file is really missing, or it's available but corrupt.

You can scan your computer with Windows Defender or a third-party antivirus program, but this doesn’t always fix the error.

If you are getting this error while opening a game or app on your Windows 10 PC, you’ve come to the right place. Because in this article, I’m going to show you 3 ways you can fix the error and start using your app or playing your game once again.

Solution 1: Reinstall the App

As suggested in the error message, reinstalling the program that's triggering the "msvcp140.dll was not found" error can fix the problem for you.

To reinstall an app, do the following:

Step 1: Click on Start and select Settings.

Step 2: Select Apps from the menu tiles.

Step 3: Click on the app causing the error and select Uninstall.

Step 4: Restart your computer, then reinstall the app by downloading it from the vendor’s website or Microsoft store.

Solution 2: Run the SFC Scan

Since the error could be triggered by a corrupt file, the System File Checker scan can help fix it. When you run this program, it checks your computer files for corruption and fixes them.

To run the SFC scan, follow the steps below:

Step 1: Click on Start and search for “cmd”. Click on Run as Administrator on the right, because you need to run the scan as an admin.

Step 2: Paste in sfc /scannow and hit ENTER.

Step 3: When the scan is done, close the Command Prompt and restart your PC.

Solution 3: Install Microsoft Visual C++ Redistributable

If any of the solutions above fail to work for you, then installing Microsoft Visual C++ redistributable package will fix it.

This is because msvcp140.dll and another DLL file called vcruntime140.dll are both constituents of the Microsoft Visual C++ package.

The following step by step guide shows you how to install Microsoft Visual C++ redistributable:

Step 1: To download the file, go to the official Microsoft Visual C++ redistributable download page and click Download.

Step 2: On the next page, you will see the option to download the file for a 32-bit operating system and another for a 64-bit operating system. Select the one for your OS and click “Next”.

Step 3: Open the downloaded file and follow the installation Wizard to install it.

If you have the Microsoft Visual Studio 2015 package installed already and you still get this error, you should uninstall and then reinstall the package.

I hope this guide helps you fix the error.

Thank you for reding.

Data Engineering jobs are in high demand. And getting a certification in the subject is a good way to learn and to deepen and prove your skills.

Each cloud provider offers a certification specialized to their Data Engineering services. They carry weight but are not easy to pass.

The Azure DP-203 is a pretty tough certification to pass. I spent about 20 hrs on an online course, 5hrs on practice exams, about 5 hrs reading, and another few hours figuring out what tools to use, how to sign-up, and so on.

My aim was to get to a pass quickly and I think it can be done quicker than I did it. Here are some tips on getting your Microsoft Azure Data Engineer certification as quickly as possible.

Pre-requisites

I have background in data engineering but I don’t think it’s required. If you don’t have this background then I’d suggest a short primer on key Data Engineering concepts (OLTP vs OLAP, data warehouses, and data lakes).

I was also very familiar with cloud computing before the course but not especially familiar with Azure’s core services. If you want a primer on cloud computing then Microsoft has one.

How to Study for the DP-203 Exam

Online Courses

I used a Udemy course by Alan Rodrigues to work through the content. I liked that it showed the tools in action, illustrated the concepts, and contained exam tips and practice tests.

There is official content from Microsoft and that looks good too. The official content is a mixture of text and video with more weight towards text, whereas courses like Udemy (and there are plenty of them) are mostly video.

I would suggest to just pick whatever you like the look of and try not to overthink the decision.

Books

I also bought a book to read on my kindle but I didn’t get much value from that and would say a course is enough.

I’d suggest checking when any material was last updated before choosing it, as the course content does change over time and books can get outdated quickly. I do find it helpful to have reading material to complement a course, I think I just didn't pick the right book for me this time.

Getting Hands-on

Microsoft, on their website, points to an option of instructor-led training but I didn’t do that. I also didn’t do any practical labs in the course that I worked through.

Others do recommend getting hands-on. I suspect it depends how much background you have and whether you feel confident that you’ve grasped the material just from watching instructional content.

Some people take notes while watching videos to help organise thoughts and make sure they’re understanding. I didn’t take notes either, though I have on previous courses.

How to Practice for the Exam

My top tip is to use the official practice exam tool. It is worth the cost.

I used practice tests on Udemy and they were helpful. But with those you don’t get feedback until you’ve completed the test.

When learning I prefer to find out what I’ve got wrong straight away. If I don't find out the answer until the end of a test then I have to remember the context of the question, which involves more effort. I also want to ensure that what lodges in my brain is the right answer and not the wrong one. For that it's better to be corrected quickly.

Features of the Official Practice Test

The official practice test lets you choose test lengths for however long you’ve got to practice at that moment. You can configure whether it tells you answers straight away or at the end.

The format of the questions matches the exam, which you don’t get with all of the practice exam sites. The exam has some multi-part linked questions and a lot of sites aren’t able to do that.

Getting Started With the Official Practice Test

The flow to buy and login to the practice test is a bit confusing. You click through from the Microsoft website and buy it from a third-party vendor (in the UK, where I am, this is mindhub). But you don’t need a login there and if you do create one you can’t use it to login to the test.

Instead you login with your Microsoft or GitHub identity to marketplace.measureup.com and register a key from the purchase.

Practice Test Books

I also used a book of practice questions and answers. That way I could do some exam prep away from my laptop.

There’s several of these books out there and I suspect they’re pretty similar. They’re not as good on quality as the practice test. I found errors like an answer section following a different question than the one it belonged to (which may be a kindle thing). The explanations also aren’t as detailed but it’s still helpful.

The one I used had a format of question and options on one page and then answers on the next. I would recommend using a book like this.

Topics I Targeted

There are certain topics that it’s worth getting clear about because it seems to me like they come up a lot and the questions are mostly of a similar format. The ones that stood out for me are:

• Storage tiers. If you know hot, cold and archive you’ll get some marks.
• Star schema.
• Slowly changing dimension types. Wikipedia explains this well.
• Distributions for Synapse dedicated tables. Expect to get asked a question with a big Fact table and small Dimension tables. The dimension tables will want replicated distribution and the big fact table will be hash-distributed with hashing on some foreign key type column used in joins.
• Difference between Synapse (warehouse with some added processing features), Stream Analytics (real-time processing), Data Lake (large-scale unstructured storage), Data Factory (ETL) and Databricks (managed Spark plus notebooks, ML and delta lake).
• When to use Parquet, Avro, Json and CSV formats. (The answer is almost always that parquet is best for querying data at scale but they also like to make sure you know avro is good for timestamped data.)
• The syntax for writing to a file or stream using Spark.

For me these were topics I felt I could bank on. There were other topics I was confident about after practicing, but for some topics there’s lots of different ways the questions can be approached. For these I felt I could see the patterns and they were fairly predictable. It’s reassuring to have some questions you can do from memory without having to think a lot.

Learning to Analyse Questions

Learning how to read and analyse the questions is a skill. Often the questions contain some incidental detail around a scenario (for example, the fact that it’s a grocery company) and some clues that point to an answer (for example, the size of their sales transaction table).

I don’t always recognise where a question is going from a first read, so then I find it useful to quickly scan the answers. This is dangerous, though, as then you might find something you recognise and match it to a clue and jump on that. But the questions can require picking up on multiple clues.

You want to eliminate the wrong answers if you can (which is tricky if you try to go fast). Sometimes there’s also multiple right answers to select and you’ll miss marks if you choose just one when there’s two correct selections (the questions do tell you when you should select multiple but it’s easy to miss if you rush or get tired).

This is why I recommend the official practice exam tool – it’s great for learning the format of questions and the skill of how to read them.

When to Tackle the Real Thing

Can I Learn It All?

The scope of the exam is so broad that it’s hard to really know everything. You’ll even get occasional wildcard questions that bring in Azure services that don’t relate to data.

When that does happen and you don’t know about those services, you can typically guess your way through it so long as you’re clear enough about the data services.

Cosmos DB is a new part of the Azure data stack. It’s not explicitly part of the syllabus right now but the exam scope is broad and it does creep in. It’s worth knowing a little about it but don’t get sucked into learning all its ins and outs.

If you try to really learn everything that could come up then you’ll have an awful lot to learn.

Databricks is huge in itself. You don’t need to know any of the Databricks machine learning stuff. You basically just need to know about setting up clusters, working with files in Azure storage using Spark, authentication and differences between Databricks and other Azure services that happen to feature flavours of Spark (Synapse and HDInsights).

So When Am I Ready?

If you’re doing well consistently on practice exams, you’ve developed a knack for guessing and can make the guesses pretty quick, then you’re ready to give it a go.

But it’s also about your comfort level – don’t let me (or anyone) rush you into doing it before you feel ready.

How to Tackle the Exam

Structure and Timing

You don’t get much time per question. For me it was 65 questions in 100 minutes. The total exam time is more than 100 minutes but 100 minutes is the time you get for answering questions.

Some questions are more than one mark per selection in the answers so the number of questions you get can vary (I believe with the number of marks available staying the same).

Basically expect to answer roughly one question every 90 seconds or so. But don’t worry about time while first practicing. You’ll get faster with practice.

I actually didn’t finish all the questions. I thought I had finished after about 60 questions and started reviewing answers. Then I realised I had to click through to a separate case study section. This happens right at the end, and although there is guidance I didn’t find it super clear. I didn’t miss out on many marks from it though as it’s not a big section.

Online Proctored vs Test Centre

Traditionally these kinds of certification tests were taken in test centres under supervision. Now the online proctored versions are popular. This was my first online proctored exam.

When I first looked at the guidance on online proctored exams I thought it sounded complicated. I knew that at a test centre there would be someone in the room the whole time and they'd be watching in case anyone had snuck in notes. I kept wondering how you can replicate this in an online proctored exam?

There are some instructions for the online proctored test about keeping your room clear and showing that you don't have any notes. These confused me and for a while thought I would need to buy an extra webcam to show that my room was clear and that I didn't have any notes at any point.

But it’s actually much simpler. I only needed to take photos of the room (using a link that gets sent) at the beginning and stay on my main webcam (a built-in one was enough). So it's just a check of the room at the beginning and then you just stay on webcam.

Good Luck!

I hope this advice is helpful to you and I wish you good luck! Feel free to ask me questions via Twitter.

What is a CDN?

A Content Delivery Network (CDN) helps you deliver your content more quickly. You can serve any type of content that remains unchanged over a period of time, like images, videos, CSS, JavaScript, HTML files, PDFs, and more.

A CDN is a group of servers that are spread across the world to deliver the content from the Edge servers. Edge servers are servers located closest to the place from where the request is being made.

Depending on the request, edge servers may either return the content from its cache or they can fetch it from the Origin Server. The servers that serve the actual content are called Origin servers.

In the above image, the Edge Servers are located around the world and the Origin Server is located in California, USA. When a request is made, the Edge Server located at Mumbai, India may contact the Origin Server if it's not able to serve the content.

How Does a CDN Work?

CDNs have four main parts: a Consumer, a DNS, an Edge Server, and an Origin Server.

When the Consumer makes a request, it is at first accepted by its Internet Service Provider (ISP). The ISP will then hit the content provider's Authoritative DNS.

An Authoritative DNS converts the DNS request to an IP request.

When the Authoritative DNS is made, it returns the IP address of the closest Edge Server. The Edge Server will then check in its own cache to see if the requested content is available.

If it is, then it returns the content. If the content is not available, it requests the content from the Origin Server and on retrieval caches it.

Benefits of a CDN

Low Bandwidth Consumption

Many web hosts have a limited bandwidth allocation per month. If you go beyond that you will be charged extra.

With a CDN most of your bandwidth will be saved since the content will be served by the edge servers.

Low Latency

The Edge Servers cache the content. So anytime cached content is requested the latency is reduced drastically. This is because the request doesn't go all the way to the Origin Server.

Security against DDoS

Almost all the popular CDN's have the capability to protect your webserver against Distributed denial of service (DDos) attacks.

Improves SEO

Loading time is one of the factors that can affect your site's SEO rankings. If you are serving most of your content via CDN, the loading times are drastically reduced and can help improve your SEO.

Deep Dive into Azure CDN

Let's say you have created an Azure Storage Account and hosted a very simple site that displays Hello World as h1. Now that you know the benefits of CDNs, you want to serve your simple site over CDN.

You will have an endpoint like https://demostorageaccountarjav.z29.web.core.windows.net/ (where instead of demostorageaccountarjav it would be your storage account's name). Here are more details on how to setup a static website.

Login to your Azure Portal and click on Create a resource from you dashboard. Search for CDN which will open the resource in the marketplace as below.

Create CDN

This will open up a form to create a CDN profile. A CDN profile is a set of CDN endpoints. There is not much to fill in here except the name, resource group, and the pricing tier.

Next, select the checkbox to create a CDN endpoint. An endpoint is where the Consumer will be requesting content. So if you have multiple sites you can create multiple endpoints as well.

I have attached a screenshot for your reference on what values to put in. Since CDN is a global service the region selection will be disabled.

CDN Details

You can now click on Create to generate the profile and endpoint. It will take a couple of minutes to create. After it is created and when you go to the home screen, you will have these 4 resources:

Azure Resources

As discussed earlier, the CDN Profile is a group of Endpoints. To view the details click the *Endpoint resource. You will see an overview with a link to the Endpoint hostname.

Endpoint Overview

When you open the endpoint hostname it might show "404 not found" initially. You might have to wait another 10-15 minutes before your actual site is visible.

As discussed in the benefits section you can configure the Endpoint for security, caching, routing, and a lot of other things. You can explore more options here.

How to Access via SAS Token

You may be wondering what to do if your resource is in a private container and can only be accessed via a Shared Access Signature (SAS) Token. Well you are in luck! The query strings are passed on as they are and since SAS is as a query string you are good.

Go ahead and create a new storage account (with static website disabled). Add a new Endpoint in the CDN profile that points to the newly created storage account.

For demo purposes I have created a container named site with private access level. And I've uploaded a Blob to it named Photo.jpeg in a Storage Account with the URL https://demostorageaccountarjav.blob.core.windows.net.

You can of course get a SAS token from the Azure portal directly for testing, but that's not how you would usually do in the real world. For that, you'll find below a simple snippet to create a SAS token in Node.js.

const azureSasToken = require('azure-sas-token');

// default token validity is 7 days
let sasToken = azureSasToken.createSharedAccessToken('https://<service namespace>.servicebus.windows.net/<topic name or queue>',
                                '<signature key name>',
                                '<signature hash>');
console.log(`sasToken: ${sasToken}`);

// Specify your own validity in secs, two hours in this example
sasToken = azureSasToken.createSharedAccessToken('https://<service namespace>.servicebus.windows.net/<topic name or queue>',
                                '<signature key name>',
                                '<signature hash>', 
                                60 * 60 * 2);
console.log(`sasToken: ${sasToken}`);

We have used a simple npm package named azure-sas-token. Once the SAS is generated your URL will look something like this:

https://demostorageaccountarjav.blob.core.windows.net/site/Photo.jpeg?sp=r&st=2021-03-25T07:28:45Z&se=2022-02-02T15:28:45Z&spr=https&sv=2020-02-10&sr=b&sig=PD4HlRI8bDEirMevpYQgpx6drwh%2BE5EpILfXkQOMlvw%3D

The above URL is pointing directly to the storage account. So go ahead and change the origin so that it uses the origin endpoint.

https://demowebsitearjav.azureedge.net/site/Photo.jpeg?sp=r&st=2021-03-25T07:28:45Z&se=2022-02-02T15:28:45Z&spr=https&sv=2020-02-10&sr=b&sig=PD4HlRI8bDEirMevpYQgpx6drwh%2BE5EpILfXkQOMlvw%3D

When you visit this site you will now be able to view the protected resource via CDN. You might have to wait a few minutes and/or purge your Endpoint to see the updated content.

Conclusion

In my opinion everyone should be using a Content Delivery Network. There are lots of other providers like Cloudflare, S3, and so on. But Microsoft is one of the major players emerging with a wide variety of services.

If you are an Azure fan like I am, you should definitely give Azure CDN a try.

For any feedback or questions you can get in touch with me.

Check here for more tutorials like this.

In this article, we’ll look at how to use join on more than two tables and aggregate the result using the function STRING_AGG() in Microsoft SQL Server.

If you don’t know about Microsoft SQL Server, I’ll briefly explain to you what it is😃. Let's get started.

What is Microsoft SQL Server?

Microsoft SQL Server is a Relational Database Management System that revolutionized how businesses handle data. It helps you store and manage data.

If you’re familiar with other relational database management systems such as MySQL or PostgreSQL, then picking up Microsoft SQL Server should be pretty easy.

I’m running on the default instance of SQL server.

Now, let’s consider a problem.

The Problem: How to Get Employee Details and Projects

Let’s say we have three tables, namely Employee, Project, and EmployeeProject. The below image is the relational database design:

Figure 1: Relational Database Design for the problem

The problem is to get all the employee details and their corresponding projects.

Figure 2: Employee table

Figure 3: EmployeeProject table

Figure 4: Project table

Things to consider: not all the employees from table Employee mapped with table EmployeeProject and not all the projects in table Project mapped with table EmployeeProject .

Our main goal is to retrieve all the employee details from table Employee whether they are mapped with EmployeeProject or not.

We can try to solve this problem by using joins. As you can see, we have to join three tables in order to solve this problem. First, we need to join tables Employee and EmployeeProject . Then we'll join the resulting table with Project .

Let’s go through some scenarios to solve this problem.

Solution #1: Use Inner Join

Let’s go with INNER JOIN everywhere!

SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId 
FROM Employee AS e INNER JOIN EmployeeProject AS ep 
ON e.Id = ep.EmployeeId

This will give us:

Figure 5

Let’s think of the above table as Employee-EmployeeProject . It contains all the employee details as well as their corresponding project ids.

With the help of Employee-EmployeeProject, we’ll be able to access the Project table. Let’s do that:

SELECT abc.FirstName, abc.LastName, abc.City, abc.Designation, p.Name AS Project FROM Project AS p 
INNER JOIN
(SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId
FROM Employee AS e INNER JOIN EmployeeProject AS ep
ON e.Id = ep.EmployeeId) AS abc 
ON p.Id = abc.ProjectId

Output:

Figure 6

Great! 😃 Now we are able to retrieve employee details as well as their corresponding projects. But our main goal is missing (that is, to get all the employee details) as we’re missing Sophia Ashley’s details.

Scenario 1 worked, but we didn't accomplish our goal. 😆

Solution #2: Use Left Join

Let’s get all the details from employees whether they are mapped with EmployeeProject or not (our goal) by using LEFT JOIN with Employee table:

SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId

This query will give us:

Figure 7

As you can see from the above figure, we are able to retrieve Sophia Ashley’s details since we’re using LEFT JOIN on Employee table with EmployeeProject table.

Let’s think of the above table as Employee-EmployeeProject . It contains all the employee details as well as their corresponding project ids (including NULL for when it doesn’t contain any ProjectId value).

Similar to scenario 1, now we can access project names since we know ProjectId . Remember, our goal is to retrieve all the employee details whether they have a project or not.

To ensure that, we’ll need to retrieve all the values from Employee-EmployeeProject when joining with Project table:

SELECT abc.FirstName, abc.LastName, abc.City, abc.Designation, p.Name AS Project FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId

Figure 8

Great work! We achieved our goal. 😃

This is good stuff. But it would be great if we were able to group these rows and return one row per employee. This is our new wish! 😉

This leads us to question how we can group these results. We can group these rows by using GROUP BY .

So, we’ll GROUP BY the results in rows by FirstName :

SELECT abc.FirstName, abc.LastName, abc.City, abc.Designation, p.Name AS Project FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName

And the output is:

Figure 9

What happened?

It says column LastName is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This error is applicable to all the remaining columns in the selected list except FirstName .

When we try to select values of FirstName and group by FirstName, it means that we’re going to group all the rows based on FirstName only and select the FirstName column. For example, let’s select only FirstName and group by FirstName:

SELECT abc.FirstName FROM Project AS p RIGHT JOIN 
(SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName

Figure 10

As you can see in figure 10, we’ve grouped all the rows by FirstName . Here, there’s no ambiguity.

Now, let’s select FirstName, LastName, and group all the rows by FirstName :

SELECT abc.FirstName, abc.LastName FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName

Figure 11

If we check out what the values of LastName are in Figure 8, we can see that we have two employees with the same FirstName but different LastName: James Johnson and James Smith.

So, when we try to group all the rows by FirstName and select the values of FirstName and LastName, it leads us to an ambiguity state.

Imagine that MSSQL asks us, “You’re selecting FirstName ,LastName and trying to group all the rows by FirstName. But the FirstName James has two different LastNames, Johnson and Smith. When selecting James’s last name, what should his LastName be? Johnson? Smith? or both?” There's ambiguity in MSSQL regarding which one to select.

To solve this FirstName and LastName issue, we can either (option 1) group all the rows by both FirstName and LastName or (option 2) enclose LastName into an aggregate function to select only one value.

Option 1:

SELECT abc.FirstName, abc.LastName FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName, abc.LastName

Figure 12

Option 2:

SELECT abc.FirstName, MAX(abc.LastName) AS LastName FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName

Figure 13

In the above FirstName and LastName issue, although both options work, option 1 makes more sense than option 2.

For more detailed information on ambiguity, check out this stack overflow question and answer!

NOTE: when you have a GROUP BY query, the selected list must be part of either the grouping criteria or must appear in aggregate functions such as SUM , MAX , COUNT and so on.

Again, back to our wish, we’ll try to GROUP BY all the rows by all columns:

SELECT abc.FirstName, abc.LastName, abc.City, abc.Designation, p.Name AS Project FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName, abc.LastName, abc.City, abc.Designation, p.Name

Figure 14

We’ve successfully grouped all the rows but we couldn’t retrieve one row per each employee, as each row is distinct from others if we consider all columns. This means that grouping them by all columns won’t work.

According to our new goal, we need records for Emma Cooper, James Johnson, James Smith, Maria Garcia, and Sophia Ashley (five rows).

GROUP BY FirstName , LastName , City and Designation will give us these five rows, but what about Project ? We can't GROUP BY it (if we do that then the result would be similar to figure 14), but we can use an aggregate (adding together) function to aggregate Project.

Actually, we can use the [STRING_AGG()](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15#syntax) MSSQL aggregate function to return one row per each employee by concatenating the Name column in the Project table and GROUP BY the remaining columns:

SELECT abc.FirstName, abc.LastName, abc.Designation, STRING_AGG (p.Name, ',') WITHIN GROUP (ORDER BY p.Name) AS Project FROM Project AS p RIGHT JOIN (SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId FROM Employee AS e LEFT JOIN EmployeeProject AS ep ON e.Id = ep.EmployeeId) AS abc ON p.Id = abc.ProjectId GROUP BY abc.FirstName, abc.LastName, abc.City, abc.Designation

Figure 15

Yay! We’ve done it. 😃 😃

The problem we discussed in this article helped us understand some of the main concepts behind Microsoft SQL Server.

Now we have a basic understanding of how to use join and STRING_AGGin Microsoft SQL Server.

Please feel free to let me know if you have any suggestions or questions.

Photo by MI PHAM on Unsplash.

Connect with me on Medium.

Please support freeCodeCamp in their Data Science Curriculum Pledge Drive.

Thank you 😇

Happy Coding ❤️

To Explore Further

1. [STRING_AGG](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) (Transact-SQL) — Microsoft Docs
2. Aggregate Functions — Microsoft Docs
3. An overview of the [STRING_AGG](https://www.sqlshack.com/string_agg-function-in-sql/)function in SQL — SQLShack
4. An in-depth guide to GROUP BY

And if you're writing in Microsoft Word, there's an easy way to add page numbers to your work. Let's see what it is.

How to Add Page Numbers in Word

When you have a Word document open, you'll see the main Word menu along the top of your screen, like this:

Main Word menu at the top of your screen.

Step 1: Click the "Insert" Tab in the Main Word Menu

Just click on the "Insert" tab, and you'll get a dropdown menu with lots of options. About two thirds of the way down, you'll see a "Page Numbers" option, like this:

Just click on "Insert" on the main menu and then scroll down to "Page Numbers".

Step 2: Scroll Down and Select the "Page Numbers" Option

When you click on "Page Numbers" a little box will pop up asking you how you want to format your page numbers.

Step 3: Format Your Page Numbers

You'll have options for where you want to place the numbers ("Position"), how you want them aligned on the page ("Alignment"), and other formatting options. It'll look like this:

How you can format your page numbers.

Step 4 (Optional): Customize Your Page Numbers

If you just click on the dropdowns by each option, you'll be able to choose exactly where and how you want your numbers to appear.

For example, if I want my numbers to appear at the bottom right, I'll just leave those options at the default. But if I want them, say, at the top and insides of each page, I can make the following adjustments:

How to customize your page numbering.

You can also choose whether you want a number to show on the first page (perhaps not if it's a title page, and so on).

If you really want to get into the details of your page numbers, you can click the "Format..." button. There you can choose how your numbers look (you can have Roman numerals if you want!), whether you want to include chapter numbers, and how your numbers should start.

More page number options.

And voilà – now your pages are numbered!

How to Find the Page Numbers Tab in Word

Here's something cool about Word: if you go to the "Help" tab to search for some functionality, it'll show you where to find it in the main menu.

Here's what I mean:

Say I want to find where to add page numbers (and didn't have this handy tutorial). I could just click on the "Help" tab in the main menu and type in "Page numbers".

But that's not the cool part – as you're typing, you'll see a match pop up (highlighted in pink below). When you hover over that option (don't click yet), Word displays where to find that tool or feature (in the red box below) and points to your query with a pulsing blue arrow! Like this:

Click "Help", type in "page numbers", hover over "Page Numbers", and the menu will pop up to the left.

Then if you actually click on your page numbers query in the Help tool, it'll just take you straight there and you'll see the "Page Numbers" box pop up.

Now you know how to add page numbers in Microsoft Word and you can customize those numbers to your heart's content. Happy writing!

According to Microsoft's first quarter results, Office 365 has 200 million monthly active users. That is a lot of users leveraging a subscription service to access Microsoft Office products.

The core apps and services included in Office 365 include:

• Word
• Excel
• PowerPoint
• Outlook
• OneNote
• OneDrive
• Teams

In this article we will dive deeper into one of these products, Microsoft Word.

Perhaps Word was the first word processor you learned how to type on. Maybe you use it daily at work to conduct research, draft important documents, or write essays. Clearly companies love and experience the benefits Word affords because this tool keeps staff thinking, communicating, and writing.

In college I remember sending myself Word files and saving each with a laughably hard to understand name (like Final Draft 1.21a). Regardless of your past associations with Word, there are ways you can start using this popular tool better today. Specifically you can use keyboard shortcuts to greatly accelerate your typing and word processing speeds.

I consider myself something of a keyboard shortcut guru and want to pass some of these lessons on to you. Unlike webinar software which is largely web based and requires simple click and point interactions, Word can be fully immersive.

Believe it or not, Word is more akin to augmented reality in that regard. When you play a piano you can push keys or you can have a more full body experience and deeply interact with the instrument. Word is similar.

If you are working from home and want to expedite the speed and accuracy of your engagement with Word, you can take time to learn about keyboard shortcuts that follow.

Of course which shortcuts to learn and when to use each depends on the type of computer you are using - and your goals.

First, a disclaimer.

Writing an article about general Word shortcuts reminds me of a quote attributed to the poet John Lydgate and later adapted by President Lincoln:

“You can please some of the people all of the time, you can please all of the people some of the time, but you can't please all of the people all of the time”.

These shortcuts will work on most of your computers for most of your use-cases most of the time.

A friendly note that it is possible that your keyboard layout, operating system (Windows or Mac), and version of the software (desktop, client, version, and so on) you are running will impact some shortcuts and the utility of this advice.

I often think about different versions of Word like Dropbox vs Box: similar but different.

With this disclaimer complete, here are a number of Microsoft Word keyboard shortcuts that you can learn today to enhance your word processing experience. So grab a seat and get comfortable as I will start with a deep dive of my favorite Word shortcuts and then cover how to leverage the Word Ribbon as well.

Microsoft Word Keyboard Shortcuts

Font Style

The three most popular formatting options for font style and emphasis are italics (Ctrl+I), bold (Ctrl+B), and underlining (Ctrl+U).

Capitalization

There may be times when, for the purposes of clarity or emphasis, you want all of the text to be UPPER CASE. But what if you just typed out a whole paragraph with caps lock on, and now it looks like you’re screaming?

There’s no need to delete it all and type it out again: just highlight the relevant text and press Ctrl+Shift+A to turn it all into regular lower case/upper case.

Text Alignment

Do you need text right in the middle of the page for a title? You can center text with Ctrl+E. After that, you’ll most likely want to return to normal formatting for the main body of your text, in which case you can align left with Ctrl+L.

Hyperlinking

This one is handy in a number of situations - not just in Word, but also in PowerPoint and Excel.

Highlight a word or sentence that you want to link to a webpage and press Ctrl+K. A dialog box will appear for you to input the web address that the text is to be linked to. Once you’ve entered the information, the selected text will turn into a blue, underlined hyperlink.

Navigation

Are you trying to find a specific section in a long document? Hit Ctrl+F for Find, which allows you to enter the word or phrase that you’re looking for and be taken directly to it.

Ctrl+F is also extremely useful in web browsers and across the Office suite.

Deep Search

Pressing F5 in Word opens a more comprehensive search tool that allows you to navigate to a specific page, section, or line of your document, as well as use the Find and Replace function.

Selecting Text

There’s no need to always use the mouse to highlight selected text. The Shift+Arrow keys will also perform this function.

Shift+Left/Right will highlight one character at a time, Shift+Alt+Left/Right one word at a time, and Shift+Ctrl+Left/Right an entire line at a time.

Word Count

Pressing Ctrl+Shift+G will instantly bring up your word count to provide you with that all-important visual affirmation that you are, indeed, moving steadily towards your goal.

Save, Open, and Print Files

Use Ctrl + S to save whatever file you're working on – and do it often so you don't lose your work!

In a browser, you can also use this to save a page for offline viewing. The keyboard shortcut for Save As (saving a file with a new name) depends on the app you're using. In Word it's F12. But many other programs use Ctrl + Shift + S. Ctrl + O will open a file into whatever program you're using.

Cut, Copy, and Paste Using the Keyboard (personal favorites)

• Ctrl + X to cut highlighted text (remove it and place it on the clipboard).
• Ctrl + C to copy text (place a copy of the text on the clipboard).
• Ctrl + V to paste text (copy the clipboard to the cursor position, and very handy for converting large amounts of content or when engaging in copywriting.
• Select All: To select everything in the current space, use Ctrl + A.
• Undo: Ctrl + Z will undo any action.

Word also has a Ribbon, much like other Microsoft products.

The Ribbon is the menu that appears when you click on each tab in the main Word menu (check out the image below if you're not sure). Here are some useful Ribbon keyboard shortcuts when writing documents in Word.

You can combine the Key Tips letters with the Alt key to make shortcuts called Access Keys for the ribbon options.

When building templates or newsletter examples with lots of colors, formatting, and design requirements, the Ribbon shortcuts are particularly powerful and helpful.

For example, press Alt+H to open the Home tab, and Alt+Q to move to the Tell me or Search field.

Press Alt again to see Key Tips for the options for the selected tab.

The ribbon groups related options on tabs. For example, on the Home tab, the Font group includes the Font Color option.

Press the Alt key to display the ribbon shortcuts, called Key Tips, as letters in small images next to the tabs and options as shown in the image below.

Microsoft Excel includes a variety of different functions that help users with calculations of any kind. The functionality of Excel is so comprehensive that average users don't even take advantage of most utilities.

However, if you often scroll through columns and rows looking for the same information, chances are that you will appreciate the VLOOKUP function. VLOOKUP, which stands for “vertical lookup,” can help you quickly find the data associated with a certain value that you enter.

For example, you may have a table that contains products with unique IDs and prices. VLOOKUP can show you the price of a certain product if you enter its ID.

You can use VLOOKUP in many different ways and it will simplify your work significantly, especially when dealing with large tables.

You don’t need to spend a lot of time looking for a certain cell because this function will find it for you. However, beginner users often find it difficult to set up VLOOKUP. Therefore, I decided to help you by preparing this detailed guide.

What is VLOOKUP?

First of all, VLOOKUP is a function. Therefore, if you’re new to Excel, you may want to familiarize yourself with some basic functions, like AVERAGE, SUM, or TODAY. This way it will be easy for you to understand how this function works.

VLOOKUP is a database function, so it’s intended for database tables. Such tables are basically lists of different items. For instance, you may use this function when working with lists of products, employees, customers, etc.

Let’s say, you have a list of products that consists of four columns. It might include the item code in the first column, the name or description of the product in the second column, and the price and the number of items available in stock in the third and fourth columns, respectively.

Database tables usually have some sort of unique identifier for each item. In this case, it’s the item code. This column is necessary for the VLOOKUP function to operate, and it must be the first column in your table.

If you’re a beginner, the first thing you should do is to understand what exactly VLOOKUP does. Simply put, it shows information from a list or database based on the unique identifier entered by the user.

If we consider the example above, this function could show the price, description, or availability of a product based on its item code. What exactly it will show depends on the formula you write. VLOOKUP supports both exact and approximate matching, as well as wildcards for partial matches.

How VLOOKUP Works

Here is the syntax for formulas that describe the VLOOKUP function:

=VLOOKUP(value, table, column_index, [range])

• value is what this function will look for in the first column
• table is the table from which the function will retrieve the necessary information
• column_index is the number of the column from which the function will retrieve the information
• range is a Boolean parameter that can be either TRUE or FALSE. TRUE is the default value, and it corresponds to approximate matching. FALSE will show you exact matches only.

Even the name of this function includes “vertical,” and VLOOKUP is only intended for tables where data is organized in vertical columns. Therefore, if you’re organizing your data horizontally, this function will be useless. In this case, you can use a similar function for horizontal lookup — HLOOKUP.

You should also keep in mind that this function only works from left to right. In other words, if the unique identifier is not in the first column of your table, the function won’t be able to retrieve information from the columns to the left of the identifier.

Every column has its number, and all columns are numbered from left to right. If you want to obtain a value from a certain column, you should specify its number in your formula. In the formula template above, this number is called column_index.

For instance, if you want to retrieve the name of the product from the example above, the column index should be 2.

As I've already mentioned above, the VLOOKUP function supports two matching modes: approximate and exact. This parameter is the fourth argument in the formula. Approximate matching is set by default. If you want to choose exact matching, you should set the lookup range to FALSE.

Therefore, both of the formulas below will retrieve data using approximate matching:

=VLOOKUP(value, table, column_index)

=VLOOKUP(value, table, column_index, TRUE)

As you can see, if you want to use the exact match mode you should be careful. If you don’t provide any lookup range value, the function will still use the approximate match mode.

The following formula will force the exact matching mode:

=VLOOKUP(value, table, column_index, FALSE)

Make sure to set the value to FALSE if you’re going to use the exact match mode. Chances are that you’ll need exact matching in most cases, so if you’re new to Excel don’t forget about this detail.

Exact match is the right choice if you have a column with the item identifier. It may also be any unique value that can be used for an exact lookup. For example, it may be a unique title of a book or movie, as well as any other unique keyword. Keep in mind that VLOOKUP is not case-sensitive.

However, sometimes you may need not the exact match but the best match possible one. In this case, you can use the approximate match mode.

For instance, you may use this mode when dealing with data tables where the necessary information corresponds to certain numerical values, and you want to retrieve results for a value that isn’t included in the table.

You can use this approach when making calculations based on the existing data. If you enter a value and the function finds the exact match, it will retrieve information from the corresponding row. However, if there’s no exact match in the table, the function will match the previous row.

Why would it match the previous row, not another one? Well, it won’t if you don’t organize your table in the right way. To enable VLOOKUP to look for the best approximate value, you should make sure that all the values in this column are sorted in ascending order. In this case, the function will just step back and retrieve the nearest value.

#N/A Errors

When using the VLOOKUP function, as well as many other functions in Excel, you may often get #N/A errors. This error means that the value was not found.

You can get this error for several reasons. Here are some of the most common cases:

• you’ve misspelled the value or added an extra space
• the necessary value doesn’t exist in the table
• you’re using the exact match mode when searching for an approximate value
• you haven’t entered the correct table range
• you’ve copied VLOOKUP while the table reference isn’t locked.

If your table has an absolute reference, it means that the rows and columns won’t change if copied. However, this isn’t the case with a relative reference. In this case, you will need to switch to the absolute reference.

You can customize the text of the #N/A error by using the IFNA function. In this case, you have to write a longer formula with IFNA that includes VLOOKUP.

Here’s an example of a formula that will return “not found” instead of #N/A:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "not found")

And here is a formula that will return a blank result:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "")

Although you can customize the message, you may consider using the #N/A error because it will immediately attract your attention and let you know if something goes wrong.

How to Use VLOOKUP

You may write formulas from scratch, or you may also use the Excel menu. Select the cell where you want to display the result, and then select the “Formulas” tab.

After this, click “Insert Function.” You will see a box where you can select categories of functions and choose the VLOOKUP function. You can also use the “Search for a function” box, and enter “vlookup.”

Select the function, and the “Function Argument” box will appear. Here you can enter the necessary parameters of the function. In this window, you should specify the unique identifier you’re looking for, database location, and the information that corresponds to the identifier that you want to retrieve.

These arguments are “Lookup_value,” “Table_array,” and “Col_index_num.” These fields are written in bold because these arguments are mandatory.

The fourth argument if for the lookup mode, and you may or may not specify it. The approximate mode is set by default.

To enter the first argument, which is the unique identifier, you can select the necessary cell and press Enter. In this case, the value of this cell will be automatically entered as the first argument of the VLOOKUP function.

Now you have to enter the second argument. The database shouldn’t necessarily start at the top left corner. For instance, you may also have a row that describes columns, which serves as a header.

“One of the best things about the VLOOKUP function is that the location of the database can also be customized,” notes Bridget Allen, an accountant at a Best Writers Online.

Given that VLOOKUP only works with numbers of columns, you should specify what area of your table you want to use for lookup. This is what the “Table_array” box is for.

For example, if your table starts at the top left corner, and its first row is a header, you can select the whole database without the first line. If your database has four columns (A-D) and five items in it, the table array will be A2:D6, because cells A1-D1 will contain the header.

You can click on the necessary worksheet tab, and select the area with the database. Press Enter and the selected range of cells will be automatically added to the second argument of the VLOOKUP function. Here is an example of a function argument:

‘database_name’!A2:D6

In this case, “database_name” is the name of the worksheet tab.

Now you only need to specify what information you want to retrieve and provide the number of the necessary column.

For example, if you want to retrieve the price of an item from the table at the beginning of this article, you should use the third row, and the “Col_index_num” value will be 3.

Wrapping Up

Microsoft Excel has a vast variety of functions that can help users deal with different calculations and other tasks. VLOOKUP is a very useful function that can retrieve information that corresponds to a certain value from a database table.

If you’re new to Excel, you may experience difficulties with this function because it has four arguments.

However, if you follow our guide, you’ll be able to select the right arguments and to use the right formulas. If you use this function a few times in practice, you will quickly understand how it works so you’ll be able to use VLOOKUP whenever you need it.

You've probably heard about Amazon Web Services and the #AWSCertified challenge. But did you know that Microsoft is also a cloud provider and offers professional certifications as well?

After passing 3 AWS certifications, I wanted to learn new things and start with another cloud provider: Azure.

My objectives were to understand how Azure works and also pass a new certification. I decided to take the first exam, AZ-900: Microsoft Azure Fundamentals, to give myself a smooth transition from AWS to Azure.

In this post, I'm going to explain the Azure certifications as well as how to become Microsoft Certified.

Let's begin.

What is Azure?

Before explaining Microsoft certifications, I'll briefly introduce Azure.

Azure is Microsoft's cloud computing platform, which gives you the freedom to build, manage, and deploy applications on a massive global network using your favorite tools and frameworks.

Azure capabilities can be broken down in 8 main categories:

1. Compute Services
2. Cloud Storage
3. Networking
4. App Hosting
5. Artificial Intelligence
6. Internet of Things
7. Integration
8. Security

Big-picture view of the available services and features in Azure

Microsoft Certifications

Microsoft offers certifications on their different products: Azure, Dynamics 365, Microsoft 365, Office, Power Platform, SQL Server, and Windows.

If we consider only the Azure product, we have 13 certifications grouped into 2 categories:

• Apps and Infrastructure
• Data and IA

Referring to the following technical job roles:

• Administrator
• AI Engineer
• Data Engineer
• Data Scientist
• Database Administrator
• Developer
• DevOps Engineer
• Security Engineer
• Solutions Architect

Microsoft Azure Certifications

Developer certification path

For each certification, you can find the associated path in the documentation.

The developer certification path requires two exams:

1. Exam AZ-900: Microsoft Azure Fundamentals (optional)
2. Exam AZ-204: Developing Solutions for Microsoft Azure

Solutions Architect certification path

The solutions Architect certification path requires three exams:

• Exam AZ-900: Microsoft Azure Fundamentals (optional)
• Exam AZ-300: Microsoft Azure Architect Technologies
• Exam AZ-301: Microsoft Azure Architect Design

If you're familiar with AWS exams, you've probably noticed there was no Associate level for the Solutions Architect role. I've summarized the exam equivalence between AWS and Azure in this table:

Comparison between AWS and Azure exams

Focus on AZ-900: Microsoft Azure Fundamentals

Microsoft says the objective of this exam is to demonstrate foundational level knowledge of cloud services and how those services are provided with Azure.

No prior technical IT experience is required. Both technical and non-technical background candidates can take the exam.

What skills are measured during the exam?

From the skills outline, the skills tested during this exam are the following:

Describe Cloud Concepts (15-20%)

• Describe the benefits and considerations of using cloud services
• Describe the differences between Infrastructure-as-a-Service (IaaS), Platform-as-a-Service (PaaS) and Software-as-a-Service (SaaS)
• Describe the differences between Public, Private and Hybrid cloud models

Describe Core Azure Services (30-35%)

• Describe the core Azure architectural components
• Describe some of the core products available in Azure
• Describe some of the solutions available on Azure
• Describe Azure management tools

Describe Security, Privacy, Compliance, and Trust (25-30%)

• Describe securing network connectivity in Azure
• Describe core Azure Identity services
• Describe security tools and features of Azure
• Describe Azure governance methodologies
• Describe monitoring and reporting options in Azure
• Describe privacy, compliance and data protection standards in Azure

Describe Azure Pricing, Service Level Agreements, and Lifecycles (20-25%)

• Describe Azure subscriptions
• Describe planning and management of costs
• Describe Azure Service Level Agreements (SLAs)
• Understand service lifecycle in Azure

How much does the exam cost?

If you were lucky enough to have attended Microsoft Azure Virtual Training Day: Fundamentals, you received a voucher allowing you to sit the exam for free.

Otherwise, it will cost you $99 USD, and you can schedule your online exam with Pearson VUE.

What does the exam look like?

From the FAQ, you have 60 minutes to answer between 40-60 questions. But I only had 32 questions in my exam. It was quite a nice surprise!

The exam presents several question types:

• Multiple choice in different forms: one correct response and three incorrect responses, select correct description, two or more correct responses out of five or more options
• Drag and drop: move the response to the correct description
• Hot area: answer questions (screenshot, picture, or diagram) in a graphical form

How to prepare for the exam

There are a ton of resources to help with your exam preparation. Microsoft recommends using its online platform, Microsoft Learn, which provides learning paths and modules you can read at your own pace for free.

Azure fundamentals learning path is the recommended course to prepare the AZ-900 exam. It's a 10-hour course containing 12 modules:

1. Cloud Concepts - Principles of cloud computing
2. Create an Azure account
3. Core Cloud Services - Introduction to Azure
4. Core Cloud Services - Azure architecture and service guarantees
5. Core Cloud Services - Manage services with the Azure portal
6. Core Cloud Services - Azure compute options
7. Core Cloud Services - Azure data storage options
8. Core Cloud Services - Azure networking options
9. Security, responsibility, and trust in Azure
10. Apply and monitor infrastructure standards with Azure Policy
11. Control and organize Azure resources with Azure Resource Manager
12. Predict costs and optimize spending for Azure

If you usually watch Andrew Brown's courses, you'll be disappointed by Microsoft Learn. The modules are plain text without any videos. Be ready to read and take notes!

I strongly recommend that you:

1. Create a free account
2. Read Azure services overview
3. Practice on your own with Azure quickstarts
4. Monitor your progress with the skills outline
5. Take practice exams

Microsoft explains what you need to know for each service in the documentation. The explanations include, amongst others, a service overview and quickstarts you can experiment to have a better understanding.

Note: using the Azure portal is enough to practice because it's not an in-depth exam.

All these steps were enough to let me pass the AZ-900 ?

This exam is not difficult to pass! It takes time and practice to prepare but nothing impossible. The thing is to find your learning routine and progress by baby steps.

Differences from the AWS Certified Cloud Practitioner exam

You shouldn't underestimate how much preparation you'll need just because you have already passed the AWS Certified Cloud Practitioner exam.

You have to start from the beginning because 80% of the exam validates the Azure ecosystem, which is different from AWS.

I don't want you to make the same mistake I made. Don't compare each new service you learn with AWS. It will confuse you more than anything else. Some Azure services don't have their equivalent in AWS and vice versa.

Forget about AWS (while you're studying) to learn Azure!

Any questions? Feel free to contact me on Twitter or directly on the freeCodeCamp Discord channel. I will try my best to answer you.

This is for those of you out there who are about to start your job search and who may be worried that you can’t land a top-tier tech job without a Stanford CS degree. Someone told you that you’re not good enough to get a job at Microsoft or Facebook.

But I’m here to tell you that you can get that job. Here’s how I landed my dream job at Twitter.

Read more about my courses here to learn how I prepared.

You can read about my experiences after a year at Twitter here.

What this article covers:

• My background
• How I landed interviews with top tech companies in the world: Facebook Google, Amazon, LinkedIn, Microsoft, Twitter, Pinterest, Snapchat, and others.
• How I landed multiple offers as a full-time software engineer
• Lessons from my interview experience
• Subscribe here for more article updates from me

If you prefer to watch my story instead, I made a video here:

Background

I did not graduate from an Ivy league school. I went to a community college in Idaho for two years, and then finished my CS degree at a small Catholic university.

I started learning computer science in my junior year of college, because it sounded fun to me at the time. The only thing resembling a computer I had growing up was a Chinese copycat of the Nintendo SNES. Even then, it would break every time I put a cartridge in it.

To support myself through college, I took multiple part-time jobs like cleaning floors and working stand-up concessions.

When I graduated, I didn’t have a job lined up. I applied to as many big tech companies as I could, and had the good fortune of landing a few phone interviews.

At this point, I didn’t have a single notion of what a technical screen would be like, much less how to prepare for it. I headed into these interviews thinking that the interviewer would ask me what a linked list or binary tree was.

I didn’t pass any of those interviews.

Moving forward

I didn’t delve too much into whether I was good. I knew that I could learn things fast. I just needed an opportunity.

As the saying goes, cast your net far and wide. So that’s what I did.

What I did next is something I’m particularly proud of. I wrote a simple Python script that scraped job listings on Craigslist with titles containing keywords from a list, and collected the emails in a spreadsheet. For the actual war story, you can read the article here.

It wasn’t the smartest solution, but people who post on Craigslist are surprisingly accurate with their titles.

Craigslist, however, didn’t like people scraping their website. To work around this, I ran my script through a VPN, and had a timer that would pause the script every few minutes or so. It wasn’t perfect, but it worked well enough.

In the end I collected about 500 emails from around San Francisco, Portland, Spokane, and Seattle. I filtered the results by how specific and recent they were, and kept improving it by adding more and more features.

As it turned out, there were a few bots in the market already that crawled Craigslist and sent out automated emails. These were mostly offshore companies that were looking to pitch their company to the US market.

One of my workarounds was that I crafted emails that used keywords from their listings in the title of my emails. I then added more details using the body of the postings to make it seem more personable. I did a quick A/B test, and the replies I received had increased quite a bit from around 2–3% to 10%.

Out of the 500 or so emails, I received about 50 replies, and landed phone screens with a small percentage of those. I stopped at 500 because I was short on time and needed to finalize a job as soon as possible. I was optimizing for results rather than reach at that point.

As luck would have it, I finally landed a job at a startup in Seattle as a junior software engineer. The startup was located in Kirkland at the time, so I had to take a 45-min bus ride to make it in time for the interview.

I then stayed there for the next 3.5 years, where I learned a great deal of stuff like Amazon AWS, EC2, DynamoDB, SQS, and Docker. I grew a lot during this period. I learned how to write modular, maintainable code. I learned how to reason about software design. And I learned how to handle people problems.

I was working next to a group of smart people who held jobs at Microsoft, Amazon, and LinkedIn, and I tried to be the “sponge” in the group. I absorbed anything and everything they threw at me. I believe this made a huge impact in my career.

Startup Days

During my stint at the startup, I worked almost exclusively on backend development, with some dev-ops in between. I started out writing some functions to add/modify a feature that were mostly small in scope. But it was a great opportunity to understand the codebase and get some code reviews.

A year into it, I started owning parts of the codebase, and then I was tasked with turning a set of features into a service. That was the start of the SOA phase for the startup. We started turning various components of the site into services, and that’s how I started learning more about RESTful services, authentication, AWS services, pub-sub, distributed systems and so forth.

The interesting part here is that I didn’t learn about any of these through books or formal education. Rather, I needed to get that set of features done and there were the bottlenecks.

So I thought, let’s go solve it!

There were many times where I was stuck in analysis paralysis — a state where I over-analyzed scenarios and ended up not able to make progress.

Those trying times were the greatest learning opportunities. I started to learn feature scoping, negotiations, monitoring, alerting, and documentation. Each step of the process revealed more things I needed to learn. I grew the most during these 2–3 years, both as an individual and software engineer.

How I prepared for my interviews

After suffering through my first job search, I told myself that I must be prepared in future interviews.

I started preparing for interviews by charting out an overview of what I was good at, bad at, and where I could improve. I broke it down into three categories: data structures, algorithms, and system design.

Having worked in PHP for most of my professional career, and C++ in college, I wanted to try something a little simpler and less verbose for interviewing.

For this reason, I picked Python. It is a great language to learn, easy to pick up, supports many data structures out of the box, and can be written quickly on the whiteboard. I learned Python by going through YouTube tutorials like these, and also reading their documentation. I prefer Python 2.x, but you can go for either 2.x or 3.

Also, another reason why I picked Python is that it’s highly readable and easy to write on a whiteboard. Here’s a trivial comparison between C++ and Python.

A C++ program to sort in descending order:

#include <bits/stdc++.h>
using namespace std; 
int main() {   
    int arr[] = {1,10,0,4,5};
    int n = size(arr)/sizeof(arr[0]);   
    sort(arr, arr + n, greater<int>());   
    for (int i = 0; i < n; i++) {       
        cout << arr[i] << " ";   
    }    
    return 0;
}

Compare that with Python’s version:

a = [1,2,4,5,1000]
a.sort(reverse=True)
print a

I’ve received feedback from interviewers to err on the side of brevity in an interview. In a 45-minute interview, you want to use most of your time solving the actual problem.

Pro tip: pick a language that’s less verbose so that you can write the code more quickly on the whiteboard.

Preparation mode

I spent about a week going through simple challenges on LeetCode, HackerRank, and Project Euler to familiarize myself with their interfaces, and to get used to writing code in Python.

The first week gave me insights into my competence level at certain programming languages. I spent another week going through some design challenges like “design X” and went as wide and deep as I could.

This was a lot of fun for me, because I often looked at iOS apps and tried to figure out how they did it. For example, how would you build Instagram from scratch? (I was asked this at Facebook.)

My background is in API designs and service-oriented architecture, so I took this opportunity to show how I would design my own version of Instagram. And because I have some iOS programming experience from my side-projects, I could talk a little bit about callbacks and push/long-polls here.

I started the conversation with some features I’d like to have on my own version of Instagram: likes, upload a photo, and a simple timeline. Feature scoping enabled me to build a very solid API because I know these scenarios well.

I then drew some pictures of a high-level design, of how the client would interact with the backend, and of how the backend would store the data.

I started small, and then added more components where needed and proactively sought where the bottlenecks were. I made educated guesses (read educated, not blind guesses) on what the requirements would be, and how each technology would fit in well. And also equally important, what technologies would not fit well.

For example, why would you use Cassandra over MySQL to store certain information (hint: scale, speed of development, schema reviews), why use OAuth over simple authentication, Redis vs Memcached for caching data, streaming vs batch processing, and so on.

There are many areas you can explore here, so typically a one-hour session is not enough. To do well on these questions, you have to read and learn about trade-offs. Pros and cons of technologies in the industry. For this, I recommend a site like HighScalability.

Take it like a typical brainstorming session with a coworker, so explore as widely and as deeply as you can.

It’s crucial to know that these design interviews are meant to explore how much you know and how well you know it, and it’s an opportunity for you to shine. I watched this YouTube video from an ex-Facebook engineer about how to solve design problems, and it gave me insights that helped me tremendously with my design interviews. My two main lessons from it: drive the design conversation, and show what you know.

I listed out my competency level for: data structures (linked list, hash map, binary tree, binary search tree, heap, array), algorithms (binary search, hashing, dynamic programming, sorting), and language-specific syntax and libraries (like sort, lambda for Python, appending, indexing).

I picked the area I was worst at, and started working on it: algorithms.

Algorithms have never been my forte. It’s been a while since my college days, and I didn’t spend much time doing binary search in my day-to-day career. I had an inkling of how each algorithm would perform, and in what scenarios to use them. But I wasn’t 100% comfortable with writing a binary search in under 10 mins. On a whiteboard. In front of an interviewer.

I also picked up a bunch of fine-point markers from Amazon, which work amazingly well. Perhaps it’s just me, but the fine-point markers in interviewing rooms usually don’t work at all. I’d usually scramble for 2–3 mins looking for a working pen, and that’s 2–3 mins you can’t afford to waste. Plus, fine-point markers allow you to write 5–8 more lines of code on a typical whiteboard vs. thicker ones. :)

Pro tip: Get your own set of fine-point markers.

I got a whiteboard from Costco for $50, some books from Amazon (listed in the tools I recommend section below), and started practicing. I made sure I ramped up on binary search, recursion, dynamic programming, BFS and DFS. A lot of interviewing questions revolved around recursion and binary search or some variations of it.

The best interviewing questions I’ve seen had many different solutions to them, and there’s an additional layer added on top as you progress through.

One Google question I had was related to file-system directories, and how to traverse them (hint: recursion). I solved that relatively quickly, and the interviewer asked how to identify a missing file in that directory. That was a little more difficult, but I got through it. And we then moved into how to rebuild the directory, how to serialize/deserialize it, and we spent a good chunk of time debating how file directories work underneath the hood. It was a very enjoyable session for me.

Interviewing at top-tier companies

It was a nerve-wracking experience, to say the least, and a real roller-coaster.

I allocated my time in the following manner: 20% resume, 20% research and 60% interview preparation.

I spent 20% of my time fixing up my resume, which hadn’t been updated in at least three years. I took a hard look at the stuff I’ve done in the past, and picked projects I handled end-to-end, regardless of complexity.

The reason for doing this is two-fold. Taking a project from start to completion demands discipline and leadership — two of the traits I’d like to be identified with.

Secondly, ownership of a project end-to-end means I can talk about each aspect of the project at length and in depth. This proved critical in helping me navigate my design round at Twitter, where they grilled me hard on not only the designs of my projects, but also the decisions behind them.

20% of my time was used for research. Research in this case meant doing due diligence on companies I was interested in and reaching out for referrals. Having referrals helps with return calls.

From my experience, I sent out 20 or so cold messages to startups and mid-stage companies, and only heard back from a handful. But, almost all the companies I was referred to by an existing employee sent me a message within a week. This is anecdotal, but there’s value to be had there.

I am not that sociable, and I didn’t know many people who’d be able to refer me to a company I was interested in. To solve that problem, I went on LinkedIn. They have a search functionality that I used to search for 1st and 2nd-level connections. 2nd-level connections are people who’re one hop away from your immediate circle. In other words, we have mutual friends who can vouch for my credibility.

LinkedIn search

This is incredibly important, because cold-calling someone for a job is very, very hard, especially in today’s market. People tend to err on the side of caution when it comes to cold-callers. Using LinkedIn was super helpful for my research phase.

Looking back at all the companies I interviewed at, here are my thoughts on each of them:

• Facebook/Google — very mechanical. The standard interviewing process, and I didn’t feel any personal connection to them.
• Pinterest — not the best interviewing experience, but a cool product and company.
• Microsoft — loved the team and especially the manager and her manager. Standard interviewing questions, but very personable. Close-second choice. Your mileage may vary, though — each team at Microsoft interviews differently.
• Amazon — standard interviewing process. About 50% of the people love it, the others don’t.
• Twitter — incredibly fun and personal. Loved the interviewing process, gave a lot of emphasis on the individual and what I’d done in the past.
• Snapchat — cool office in LA, great bunch of people who decided to jump on the startup bandwagon. Felt like things were shrouded under a cloud of secrecy.
• Lyft — near to where I live, nice office, standard interviewing process. No strong feelings about it.

Let’s talk about my favorite

In many ways, I’d say Twitter’s interviewing style was hard. But at the same time, it was more interesting and personable than other companies I’ve interviewed at.

Their interviewing process starts with an introductory phone call with an engineering manager. That’s followed up by one or two technical phone screens, depending on how you perform. If you do well, they’ll fly you out to the office you’re interviewing for, which was Seattle in my case. There are three 1-hour-and-15-minute rounds, each with two interviewers.

The first two technical phone screens are the standard, run-of-the-mill technical screens where you solve coding problems on a shared coding doc.

The onsite rounds, however, are much more conversational and feel much less intimidating. The interviewers will ask you in-depth questions about your past projects, and they’ll grill you on what you’ve done in the past. If you claim ownership of a project, you should expect some questions about it. You’re encouraged to use them for references and to bounce ideas off of.

I never felt any pressure to magically come up with a fully working solution, and it felt highly collaborative.

The others

In comparison, interviewing at Facebook and Google felt much more mechanical. They have one or two technical phone screens, and five to six onsite coding rounds. Each round involves some coding on a whiteboard, and you’re expected to come up with a near-perfect solution in a reasonable amount of time.

Facebook has two coding rounds, one design round, and one behavioral round.

I went through an additional shadow round at the end of the day, which didn’t count towards my overall score.

Google had five coding rounds, none of which focused on designs, and not a single interviewer asked about my previous projects. I don’t necessarily think this is bad. But I think it felt very mechanical and didn’t give much opportunity for the engineer to show what they’re capable of. Some people do well in these scenarios, much like some students do well in exams.

I did not enjoy my interview with Pinterest. I think the product itself is interesting, and their engineering team seems to be working on very cool technical problems. But I definitely had a negative experience during my interview there.

Pinterest has three coding rounds and one design round. Of those four rounds, the design round was most disappointing to me. Here’s why:

The interviewer came in late, and he spent a few minutes glancing over my resume before proceeding to draw some APIs on the board. He gave a short description of what he expected the API to do, and asked how I would solve it. We clarified the features of the API, and I started describing my solution using the whiteboard. About 5 minutes into it, I turned around and saw him taking a nap!

Not cool.

I gave the recruiter my feedback in a survey, and I didn’t hear back from them after that.

I won’t delve into specifics of the questions I was asked during all the interviews. Instead, I’ll share some of the insights and useful tips I learned from my preparation process.

What I learned:

• Be honest on your resume. Most companies will ask you questions about your resume, and they can tell if you made it up. It’s better to be able to know 100% about one project than to know 10% about 10 different projects.
• One-page resumes are recommended. This is especially true for tech companies, and it seems that the wisdom within the tech sphere is that you should reserve two pages and longer for post-doctoral work, or if you’ve done a lot of projects that you know and care deeply about. A friend of mine runs a company called Jobscan that scans resumes and makes specific, actionable improvements on them. They’re pretty awesome, so try them out :)
• Socialize and establish a network. There’s a lot of competition for software engineering jobs, and these top tech companies are filtering through thousands of resumes a day. Having a referral will help you get some eyes on your resume.
• Nail your pitch. Every company that’s interested in you wants to know why you’re interested in them. A bad answer: I just need a job right now to pay bills. A less-bad answer: I was browsing online and found you guys. Sounds like you’re working on interesting things. A good answer: I know you’re doing some interesting work in X to achieve Y. I’ve done some work in the past and here’s what I learned about A, B, C that might be related to X. I am passionate about Y because blah. (Don’t use this as a template. Instead, you should see the pattern here — do your research, use your background, and show the company why both of you would fit well together.)

Some more advice

Technical interviews are incredibly difficult, and sometimes it’s a hit-or-miss. The best opportunities, however, are reserved for those who are prepared.

• Prepare early, prepare well. Everyone knows that they should prepare for an interview, but most don’t know how to do it well. As with anything worth doing, it takes deliberate practice to do well at something. And deliberate practice means you need to have a system.
• Build a system to practice technical skills. I started by rating myself from 1–10 on how good I was, and worked on the ones I was worst at. I spent days on different types of questions until I fully mastered each concept. And I wrote notes daily on Evernote. I had a note that serves as a brain dump for all things programming. It is full of programming tips & tricks, common errors and misconceptions, frameworks for solving specific types of questions, and much more.

My notebook

• Keep a notebook of the things you’ve learned. I use both Evernote and OneNote to keep track of things. OneNote for technical stuff/code, because I like that I can easily format the note any way I like. I use Evernote for essays/thoughts. The image above shows a note I keep on architecture and system designs.

Evernote for thoughts/tips

• Jot everything down, even if you don’t think you’ll use it. I tend to forget very easily, so anything that I learn I write it down, including shell commands. I read technical blogs from time-to-time, and if I find anything interesting I jot it down on Evernote right away. I’ll revise it every week or month and reorganize accordingly. This has helped me tremendously over my career.
• Get mock interviews. This was definitely very valuable and I highly advise it. I had mock interviews with friends and tried to practice as much as I could. If you can’t find friends to practice with, then I recommend Refdash, which is an Interview-As-A-Service. They have a group of interviewers who work at big tech companies like Google, Facebook, and Microsoft. These interviewers will assess you on your coding and design skills. The best part of it is they’ll give you a score at the end of it with specific actionable items on how to improve.
• It’s OK to fail. I failed multiple interviews during this whole process. Sometimes you just have a bad day. It’s not the end of the world if you fail. Companies are biased towards saying no because it’s a lower risk for them. A false positive costs more than a false negative in the long run. The first few rejections definitely stung the most. I failed multiple phone screens when I first started interviewing, and my confidence level sunk. I had doubts in my mind about my abilities and started fearing that my skills weren’t relevant in today’s job market. However, I gave myself a tip: If you fail 10 times, then try 10 times more. All you need is one success. That reassurance gave me a lot of confidence to keep pushing through and when my first offer came through, the other offers came much more easily.

It took me about 2 months of deliberate practice and preparation for my interviews. I spent about 20 hours/week, or 80 hours/month, learning and writing notes on top of a full time job.

To build up my resume, it took 3.5 years of focused, deliberate work. I intentionally picked things that were tough and icky so that I could learn more than anyone else. Even though I don’t have a brand name university or top-tier tech company on my resume, I made up for it with a clear, thorough understanding of the projects I worked on. And this was possible because I researched and wrote down notes of everything I learned, and have a system to review them.

Remember: the strong survives, the tough thrives.

TL;DR: Don’t give up, set yourself up for opportunities, practice a lot, and stay hopeful. Focus on the process, and take a disciplined, dedicated approach to the process.

Tools I Recommend

• Designing Data-Intensive Applications: Awesome book for learning about scaling distributed systems! Highly recommended.
• Elements of Programming Interviews: Great for solving coding problems.
• Cracking The Coding Interview: Great for covering foundational CS coding problems.
• Daily Coding Problem.com: This is a free-to-try website that offers free daily coding problems. You can sign up for interesting daily coding challenges, and you can pay for solutions if you want.
• Dropbox: I keep all my files, pictures, resume here. Easy access, installed once and available everywhere. Love it ❤️ (If you sign up thru this link, both of us will get free 500MB!
• CodeRunner: I love this Mac app! I used this multiple times to run ad-hoc Python scripts/functions and it just works amazingly well. ?
• Kafka the Guide: I used this book as a reference guide, and enjoyed it for the high-level description.

(I share more resources I personally have used and recommend on zhiachong.com, if you’re interested in learning more.)

Thanks for reading my story! You can find me on Twitter and LinkedIn. I would love to connect and talk more about tech, startups, travel :D

Credits:

Brandon O’brien, my mentor and good friend, for proof-reading and providing valuable feedback on how to improve this article.

YK Sugishita, an up-and-coming Youtube star who left his job at Google to pursue his dreams, for proof-reading and giving critical feedback.