Performing SQL operations on the magic table

You can test your magic table by performing SQL operations on it.

Now that you created a magic table with the SQL view feature, you can test it with SQL SELECT and WHERE statements.

The following are examples of various SQL operations you can perform on the magic table mt_date_hour_cross:

SELECT * from mtdemo.date_hour_cross; returns an average of all hourly data for the every day in the time period.

SELECT DISTINCT year FROM mtdemo.date_hour_cross; returns a list of distinct years in the table.

The field year can be used as a selection list for a filter in Power BI.

Similarly, SELECT DISTINCT state FROM mtdemo.date_hour_cross; returns a list of distinct states in the table.

SELECT * FROM mtdemo.date_hour_cross WHERE year='92' AND state='CA'; returns a cross-tabulation of all hourly data for CA for every day in the year 1992.

You can perform similar types of filtering in the user interface of Power BI.