1/28
Looks like no tags are added yet.
Query
The identification and transformation of data to answer a question. Usually have parameters (constraints) that need to be meet. They use tables or other queries as their data source. PBI does not distinguish between tables and queries.
Businesses use queries to answer business questions and generate reports for decision-making. Data that is queried can be formatted for a report if it is at the appropriate level of aggregation.
How are queries used in organizations?
When translating a business question into query requirements which need to be incorporated into the query, it is important to dissect the key parameters.
How to determine query requirements?
Parameters
Important elements of the question that need to be part of the query. Ex: How many orders occurred each month in 2018?
Import tables → clean and transform → load to data model → create relationships → analyze
How can a data model be used to create a query
Single-table Queries
Use only one table as their data source. Select the existing table that contains the data needed to answer the business question. PK and FK have different meanings.
Aggregation
Using the Statistics menu in Power BI, an entire column of data can be aggregated (or summarized) into a single value: via sum, min, max, median, average, standard deviation, count values, count distinct values.
Sum
Returns total of the column.
Minimum
Returns lowest value in the column.
Maximum
Returns highest value in the column.
Median
Returns median of the column’s values (middle value).
Average
Returns average of the column’s values
Standard Deviation
Returns standard deviation of the column’s values.
Count Values
Returns the number of values in the column.
Count Distinct Values
Returns the number of different or unique values in a column.
Summarize data into a single scalar value.
When do you use aggregation functions in Power BI
Differences between count and count distinct values
If there were 5 Products. The Count of Product ID would be 5. For size ounces there are 5 sizes (18, 16, 12, 8, and 8)—> Count distinct would say 4.
Aggregating within Groups
Using the Group By menu, specify a column that contains the grouping column and what column should be aggregated and how. Any type of aggregation that can be performed on an entire column can be performed within groups. The query automatically determines how many groups exist by examining the grouping column’s distinct values. Example: Group By Category ID —> Operation: Average Column: Price.
Sorting
Arranges the rows in the query by examining the values in a specified column.
Sort Ascending
A to Z or lowest number to highest number.
Sort Descending
Z to A, highest number to lowest number
Filtering
Removing values that meet specified criteria.
Text Filtering
Can specify the entire cell contents (is), partial cell contents (contains), or starting with a specific character (begins with).
Number Filtering
Can specify operators: equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.
Equals vs Contains
The equals means that Power BI will pick up only instances that look like what you are looking for. Ex: If something equals 1, it will only find the 1s. The contains means Power BI will look for a certain word in a string. Ex: The word Chocolate is in Chocolate Cake, Chocolate-chip cookie dough, Chocolate Ice Cream.
Multiple Filtering Criteria (AND, OR)
Multiple filters can be specified using the Advanced Menu under Filtering. Each criteria referred to as a clause. AND requires both tests to be true. OR requires only one test to be true.
Moving information into a single table so that single table queries can be applied.
Goals of multi-table queries.
Append
If multiple tables have the exact same columns and store similar information this is used to form a single table.
Merge
For queries that require data from multiple different tables, the table must first be this. This selects all rows from both participating tables or queries as long as there is a match between the specified columns.