Power BI: Using SQL Query Instead Of Power Query To Get Data From Any Data Source
DescriptionThis video is a tutorial on how to use SQL query inside a Python script to extract data from a CSV file or any other data source, instead of using Power Query in Power BI Desktop. The video is divided into several sections.
The first section explains that when using the Get Data feature in Power BI Desktop, the data is selected using Power Query, even if the Power Query window is not used. However, not everyone likes Power Query, or sometimes data needs to be loaded using SQL queries instead.
In the second section, the instructor explains how to use SQL queries inside a Python script to get data from a CSV file. First, the Pandas and Panda SQL libraries are imported. Then, the Pandas library is used to read the CSV file into a data frame. After that, the Panda SQL library is used to select data from the data frame using SQL queries. Several SQL queries are demonstrated in the video, including selecting sales by store, sales by region, and monthly cost and sales by state.
In the third section, the instructor demonstrates how to import the tables created by the SQL queries into Power BI Desktop. The tables are shown in the Navigator window, and all of them are selected and loaded.
In the final section, the instructor demonstrates how to create a dashboard in Power BI Desktop using the imported data. A title is added using the Text Box visual, and a donut chart is created to display sales by region. A line chart is also created to display the sales of Oakland store by date. The date field is added to the X axis, and the sales field is added to the Y axis.
Overall, the video provides a comprehensive tutorial on how to use SQL queries inside a Python script to extract data from a CSV file or any other data source and import it into Power BI Desktop for creating dashboards.
CSV file: https://drive.google.com/file/d/1l0_4yzeLraXtTWbIrZ8v4fqMoxADo-MY/view?usp=share_link
import pandas as pd
from pandasql import sqldf
df = pd.read_csv(r'F:\ViSIT\Power Bi\Drill Down\Drill Down Data.csv')
Oakland = sqldf('select date, store, sales from df where store = "Oakland"')
Regions = sqldf('select region, sum(sales) as Sales from df group by region')
Missouri = sqldf('select substr(date,1,4) as Year, state, cost, sales from df where state = "Missouri"')
Missouri = sqldf('select Year, state, sum(cost), sum(sales) from Missouri group by year')
00:52 - Getting Data using SQL Query
06:12 - Creating a Dashboard using Data imported through SQL Query
#PowerBI #Python #SQL #DataAnalysis #DataVisualization #Pandas #PandaSQL #CSV #Dashboard