Fetch Data from Azure Data Explorer of the Kusto Microsoft Fabric Database in Excel

Hi everyone, in this video, I'm going to show you how to use Microsoft Excel to connect to datasets in Azure Data Explorer of the Q2 database created in Microsoft Fabric

Thereafter, I'm going to show you how to load the data into the data model after we perform transformation in the Power Query, write maybe one or two DAX formulas and then create a report in the pivot table

So let's get started. Now, basically, this F transaction is a data that has already been ingested into the KQL database in Microsoft Fabric

How do we connect to that data? It's super simple. So I'm going to come to Excel and then in this blank workbook, I'm going to come to the data and then click on Get Data

And then I want to point to the From Azure and then I want to point to the From Azure Data Explorer

So I'm going to click on that. Alright, now in the Azure Data Explorer for Keystone, we need the cluster

So I'm going to go to the KQL, the database, and then I want to point to the database here

Copy this query URI. So copy to the clayboard, go back to Excel, and then in the cluster, Ctrl V to paste, and

that's the cluster. And then I can optionally provide the name for the database

Now the name of the database is the same thing as database

And then go ahead and click OK. Now Excel is going to require you to sign in to your organization which I have already

done so we've been able to connect to the F transaction. Amazing

That is super awesome in Excel. Now I'm going to click on Transform Data to perform some transformative tasks and then

we can create dimension table from the fact table and then load into the data model

Now you can see we have the date column as the text or string data type so I going to switch to date Now the moment I do that you can see that we have so many nodes Now this is one of the problems I discover when creating the database and table in the

KQL in Fabric. Of course, I'm going to flag this later. Now, what I'm going to do is very simple

I'm just going to clear or filter out the nulls so that I can work with real data without

any errors. So, there we go. So, we're able to transform data and let's transform this price and this sales to a new

data type let's use currency and that's awesome so this is going to be the fact

table so what I'm going to do is I'm going to right click and duplicate so I

want to create dim tables for the product and payment types I'm going to

grab these two columns all right right click and choose remove other columns

that's cool so again I'm going to right click and duplicate this query and then

I want to get rid of this payment first so remove the column and then this is

be the deem product okay so let me just remember i'm going to call it deem product okay click enter

and then i want to get rid of the duplicate record so remove duplicate amazing and then i'm going to

come to the add column tab and under the generator i want to create an index column so click on this

drop down and choose from one and i'm going to call here c i'm going to call here c and just

adding ID so that's gonna be product ID click enter amazing so I'm gonna come

to these and do the same thing for the payment type so right click remove the

column and they want to get rid of the duplicate values so the duplicates again

in add column type want to choose index column from one and could be called it

on payments tie ID okay click enter that's cool so let's rename these as it

in payments time okay all right click enter okay and let's get rid of this

space alright so this is gonna be the dim product and dim payment type and then we have the F transaction the fact table that cool so let come to the home tab i want to close and load to want to load into the power pivot data model so i

going to choose from the input data wizard only create connection and then want to add to the

data model the power pivot so click ok and then we're going to see the queries and connection

taxpaying with three queries being loaded into the data model so let's wait all right so we can

see 126 rows loaded in the f transaction table and we have six and eight rows in the payment type and

dim products dim tables respectively so that's cool i'm going to click on this icon the power

pivot and i'm going to switch to the power pivot the data model and then we can see the table

which is quite amazing so what i'm going to do is very soon i'm going to come to the diagram view

and create many to one relationship from the dim tables across to the fact table so i'm going to

come here and then grab this payment type and connect and it's going to create a one to many

relationship and again for the product from the dim product i'm going to connect to the product in

the many side the f transaction facts table that's cool so let's switch to the data view and let's

just write a simple DAX formula. So I'm going to call this one total sales, then equal to

and then we can use the name of the DAX formula or function I want to use. I'm going to use the

sum x and then for the table, we want to iterate over the F transaction table, comma, and then for

the expression, we want to grab the sales column in the F transaction table, close the bracket

and we'll click enter on the keyboard, and then we can apply under the formatting, we want to choose

the GBPound currency symbol and then we can go ahead and create

Pivotable report. Now we can use the existing worksheet. Click OK and then we can go ahead and search for the total sales

measure drop in the values and then let want to see payment type grab that from the dim payment type drop in the rows and there we go So we can see the total sales by pivot type and we can just fix this

The people table design by choosing the report layout Showing tabular form and we can see the descriptive name of the field the payment type and then consider total sales

now let's switch back to the Power people let's want to see the percentage of

grand total so I'm gonna come to the formula bar I'm gonna call it percentage

of GT for grand total now in this case I'm gonna use the divide DAX rules we

need colon equal to the divide now for the numerator I'm gonna call the

calculated total sales DAX measure comma and therefore the denominator I'm gonna

use the calculate DAX function and then for the expression I'm gonna call it

total sales DAX measure that we created. Now to alter or to modify the filter

context of the calculator, I'm going to use the all DAX function which returns all the

rows in the table or all the values in the column. So press the tab key and

therefore the name of the table I'm going to call the F transaction table, close the

bracket for the all DAX function and then for the calculate and then for the

divide DAX and then click enter and that's going to show 1%. Amazing! So we're

to show and format this number in percentage and there we go so let's switch back using the switch

to excel and then we can see the calculated dax measure and just drag and drop in the values and

there we go so we can see the percentage of grand total and we can easily tell that the visa card

payment type represent 22.28 percent of the 100 percent of the total sales revenue generated so

basically we've seen how to connect to the azure data explorer of the keystone database and then

we'll be able to use that data to perform ysis in excel i hope you enjoyed this video if you do

please like this video share and comment and subscribe to the channel thank you and bye for now