In this video, we'd like to see how
to create this crosstab report.
And we want to look at the DAX functions count rows
and calculate.
And we want to see how to use Power Query to get the text
data.
We'll also see inside of Power Query
how to create a date table with this M code all inside of Power
BI.
Now, we're using Power BI Desktop to create this report.
This is a free download.
And there's two different ways you can download it.
The first one, you'll have to manually update
to get the latest version each month.
The second link automatically updates each month.
Below the video, you can download the files.
And unzip this folder.
Inside is a Start folder and an extra TXT file.
Double-click.
Inside the Start folder are two TXT files.
Each one contains a month of data.
Inside each text file is a proper data set
where the delimiter is a tab.
I've opened up a blank Power BI Desktop file.
So I use F12, Save As.
I'll call it something like CrossTabReportEMT1659.
And notice that's the file extension.
Click Save.
Now, just as we did in the last three videos,
we use the same data to create the same crosstab report.
Now we'll create the same report over here
in Power BI Desktop with the advantage
that we can publish it.
And then people can view it online.
And of course, we'll get to see some DAX formulas and some M
code.
Now, the first thing I'm going to do
is double-click this page report.
Call it something like CrossTab, and Enter.
Now we're going to use Power Query.
And it's almost exactly the same over here in Power BI Desktop.
Data and queries, that's our Power Query.
Get Data, down to More.
And in the Get Data dialog box under All,
there it is, from Folder.
Double-click.
This dialog box looks the same as over in Excel.
Click Browse.
And we're going to find our Start folder.
Right now, that contains two TXT files.
What we're doing is we're telling
Power Query to import everything inside of that Start folder.
Click OK.
Now, that folder path is hardcoded in,
but whereas in Excel it's somewhat difficult to update
a hardcoded folder path like this,
later we'll see that it's easier in Power BI Desktop
by using the Transform Data dropdown.
We're going to click OK.
We can see a preview of the two TXT files.
We want to come down to Combine.
And because the structure in those TXT files
is consistent and the same for each table,
we're going to say Combine, which
will build a bunch of queries to combine the tables
and append them one on top of each other.
And we want to transform.
So I'm going to click Combine and Transform Data.
It first wants to know what the delimiter is.
Tab is our delimiter.
Click OK.
It opens up the Power Query Editor.
On the left, it created a bunch of queries and steps
to give us our final table all the TXT files
combined or appended.
Over here is the name of the query.
We're going to rename it, something like All TXT Files
One Table.
Here are the steps for the query that
combined all of our TXT files.
The last step is change type.
Now, we don't need this column right here, source.name.
This is actually the file name which is sometimes useful,
but for us, we don't need this.
So pointing to the column header, right-click, Remove.
It adds that step.
Now, we'll come back later to Power Query.
But now we want to close and apply.
When we click this button, it closes the Power Query Editor
and loads the table to the data model.
Over in data, there is our table down in model.
We don't have multiple tables or relationships.
Over in report, if our goal is to create a crosstab report,
we come over to Visualizations, and we want to click Matrix.
Off to the side, we can see our matrix.
We use our diagonal white arrow cursor to move.
Resize it.
Now, in this crosstab report we're
going to have a bunch of conditions and criteria.
But right at the top, I need year and month.
If we look over on the right, here's fields.
That's our table.
Now, if we expand this, transaction date.
Now, anytime you have a data model,
we should have a separate date table.
Now, over in Excel, if I dragged a date down to rows,
it would automatically group if we
had our data in the data model.
But that process would add extra DAX columns to the fact table.
Over here in Power BI Desktop, if I drag this to the rows,
it will group it, but it adds a hidden date table.
Now, for a small data set like we have,
if you want to drag and drop, no problem.
But I'm going to show you how to create a date
table using Power Query.
Now, up in the Queries group in the home ribbon
tab, Transform Data.
That used to be Edit Queries.
We click the dropdown, Transform Data.
Now, the table we appended has a date column that will always
update when new data arrives.
We want to look this column up, and then
create a date table from it.
Over in the gray on the left, right-click
New Query, and down to Blank.
Over here I see Query1.
I click and F2.
This will be our dDate table.
Enter.
Up in the formula bar, equals, and then I
type the name of the table.
If I hit Enter right now, it just grabs everything
from that query and brings it over into this new query.
This is referencing that query.
So when new dates are added, those new dates
will be here, too.
But we don't want the whole table,
so we use our field access operator, square brackets,
and we type the name of the column,
hopefully I spell it right.
And the field access operator will extract that column
as a list.
When I hit Enter, there's the list of dates.
Now, it's important that it's a list,
because we're going to use functions
like List.Min and List.Max.
And those aggregate functions require
a list to make the calculation.
That's the first step, Source.
When I click f of x, it refers to the previous step, which
is a list of dates, List.Min.
I'm going to have to get the right case.
Open parenthesis, close parenthesis, and Enter.
Now, from that I need to extract the year.
So I use another M code function, Date.Year,
open parenthesis, close parenthesis, and Enter.
So from whatever column of dates we get,
that formula will always give us the min year.
Now I need the first of the year.
And an important requirement for date tables,
you have to have all the days for every year that
exists in the fact table.
That's because many of the built-in DAX functions
depend on that.
So after the equals sign, we need
our date function like there is in Excel,
but here it's pound, and then little date, all lowercase.
Then it wants, just as over in Excel,
the same order, year comma month comma day close parenthesis.
When I hit Enter, that'll always give us
the first date and the earliest year in the fact table.
Now, I definitely want to come over here.
F2.
And this will be Start, and Enter.
Now, the cool thing is I can copy, Control-C, f of x.
To add a new step, highlight everything, Control-V.
And we'll change in to ax.
And then instead of month 1, we want 12.
And instead of day 1, we want 31.
And then enter.
F2.
We'll call that End.
Now we have start and end.
Now we just need to create every day in between those two days.
So up in the formula bar, we click.
And just to show you a cool trick
here in Power Query, curly brackets,
that means give me a list.
If I do 1, dot dot, please go all the way to 10,
that gives me a list from 1 to 10.
But of course, that's not what we want.
We want start date, dot dot, end.
Now, that's not going to work, because those are dates.
And the curly brackets need actual numbers,
meaning the serial numbers under a date.
So for each one of the start and end dates,
I'm going to use number dot from.
That'll just convert it from whatever
it is, text number, date number, to an actual number.
And then at the end, close parenthesis.
And that M code right there, when I hit Enter,
those are all the serial number dates
from the first date in that fact table to the last date.
Now, this is a list.
And we want to convert it to a table.
So I'm going to use this convenient button up here,
To Table.
We don't have a delimiter.
We don't need to handle extra columns.
Click OK, and Enter.
We can double-click.
And we'll call this date, and Enter.
Click the data type icon, data type Date.
Now, that's the hard part.
Now we can go over with this column selected, Add Column.
In From Date & Time, the Date dropdown.
I want to start with month.
Select Date.
Back over to the date dropdown.
I want the name of the month.
Now, up in the formula bar, I'm going to click the dropdown.
I only want the first three characters.
So right after each and before Date.MonthName, Text.Start,
open parenthesis, and then right after the close
parenthesis comma 3, close parenthesis, and Enter.
Now, I forgot year.
And I want year right there.
So I'm going to come up to change type
with this column selected.
Date, Year, and Year.
Insert a step.
Yes, please.
Now I can see the rest of the columns are there.
Now, we might add many other date attributes,
but that's all we're going to need.
Now we can go up to Home, Close, and Apply.
This will load it to the data model.
In data, over here we see our new date table.
Down in model, here's our fact table.
Here's our one dimension table.
I'm going to drag date over to transaction date
to create [INAUDIBLE].
Now, the truth is this data model, if we had large data,
we probably would bring these out,
like disposition and description,
into dimension tables.
But we'll get away with dragging and dropping
in the small data set.
I just didn't want to drag and drop and create a hidden date
table somewhere.
Now let's go back over to Data.
Here's our date table.
We need to select Month Name.
And sort Month Name by Month Number
so the labels will sort correctly in our reports.
From the Sort By Column dropdown,
we select Month, which is our month number.
Now, we don't need to show this in Report View.
So right-click Month Number, Hide In Report View.
Now we can come over to Report.
Select our matrix.
Here's our date table.
We're going to drag year to column, month name to column,
description to column.
And over here on like a pivot table
we don't automatically see everything.
Expand all down one level.
That gives us the month.
Click again.
That gives us 1, 2, 3 conditions, a unique list
of descriptions for each month.
Now we drag Name down to Rows.
Select somewhere in the white, because we
want to add a slicer.
Here's our Slicer button.
And the field we're going to use in our slicer is Disposition.
We can resize, move.
On the right, we click on the Format button or the paint
roller.
Down to Border.
I'm going to select On.
Change the size.
Same with this matrix.
Now we want to come up to Textbox.
Hours less than zero.
Something like 32 centered.
Now I'm going to point to the edge
and move this above those convenient red lines.
Now we're ready to build our DAX measure.
Our goal of the DAX measure is to count hours less than zero.
Now, in the work hours column, some of the numbers
are negative.
Some of them are positive.
The first step for our crosstab report
is to simply count the numbers for each intersecting cell.
So right there, we need name equal to CZJ.
New cases, January 2020, and divide.
The way we do this with the DAX formula
is to count rows in a table.
So I'm going to right-click our fact table
and point to New Measure.
Up in the formula bar, we type the name,
Count Hours equals sign.
And the COUNTIFS in DAX is COUNTROWS.
And then we give it the name of the entire table.
Now, the reason that this works is because of filter contexts.
Close parenthesis, and Enter.
Because the formula is not in the matrix,
that formula is looking at all the rows in the table.
But if we come over and drag the measure to the values
area of the matrix, if I click off
to the side and right in that cell
right there, what count rows and filter context is doing
is count rows starts by looking at the entire table.
And then internally inside the data model,
the table is filtered down to CZJ, then old cases, then
January 2020, and finally down to divide.
The DAX formula is only looking at a table that has three rows.
That's why with filter contexts when you're
counting transactions with a bunch of conditions
or criteria, you use the COUNTROWS function.
Now, if that's the case that these conditions and criteria
are influencing and filtering the underlying table,
we need to change that filter context
so that it's not counting all the numbers, but just
numbers less than zero.
So we come back over to our fact table.
Right-click New Measure.
And we'll name this measure something smart.
Equals sign.
And the amazing function in DAX that changes the filter
contexts is CALCULATE.
Now, the first argument expression
we have to put the measure, count hours comma, and then
in filter, we put the additional condition or criteria.
So I type all.
And I'm going to find the actual hours work column, Tab.
And then we ask the question, are you less than zero?
That additional filter inside of the filter argument
of CALCULATE will be added on top of the conditions
on criteria coming from our crosstab table.
Close parenthesis, and Enter.
Now we come over to Values and X that measure out.
And now we can add our new measure, Values.
And there now we can see for that particular set
of conditions and criteria there are two numbers less than zero.
Now we can come over, select a different item
from our slicer, and the report updates.
Now, the last thing we want to do,
which we can do in Power BI Desktop, is publish.
Now when I click this Publish button,
for those of you doing this the first time,
you can sign up for the free trial.
Otherwise, you need to have a Power BI pro license.
Now, when we click Publish, I want to save it.
First, we select a particular location.
I'm going to select MSPTDA, Select.
And now from here we can go directly to PowerBI.com.
And there's our report.
We can filter it.
Published at PowerBI.com.
Now, over here we can click Share,
enter our email addresses, and an email message,
and send this report off.
And then they can view it on any device.
That was a lot of fun with a crosstab report
over here in Power BI Desktop and Power BI Online.
We saw how to use the COUNTROWS function.
We also saw how to use CALCULATE to change the filter context.
We saw how to use From Folder to import and append
multiple TXT files.
And then we saw some M code to create a date table.
Now, one last thing, over here in Power BI Desktop,
it's a little bit easier to change the folder location
if that folder of TXT file changes.
We simply click Transform Data.
And it's right on the face of Power BI Desktop.
This option, Data Source Settings, that does
exist over in Excel, but you have to open up the Power Query
Editor to get to it.
So I'm going to click Data Source Settings.
There is our Data Source.
And if we change the source here,
this will update it throughout this Power BI Desktop file.
If you like that video, be sure to click that Thumbs Up,
leave a comment, and subscribe, because there's always
lots more videos to come from ExcelIsFun.
And if you want to check out some of our other methods
of creating this report, or you want
to learn more about Power BI Desktop,
check out these videos.