List the most common techniques for data shaping in Power BI?Power BI interview questions and answers 269

 List the most common techniques for data shaping in Power BI?

Here are some of the most common techniques for data shaping in Power BI, primarily performed within the Power Query Editor:

1. Renaming Columns and Tables:

• Assign meaningful names to columns and tables for clarity and better understanding.

• Use clear and consistent naming conventions to improve readability and maintainability.

2. Removing Columns:

• Eliminate unnecessary columns that don't provide relevant information for analysis.

• Reduce the size of the dataset and make it easier to manage.

3. Changing Data Types:

• Ensure columns have the correct data types (e.g., text, numbers, dates, etc.) to enable accurate analysis and calculations.

• Fix any inconsistencies in data types.

4. Filtering Rows:

• Select specific rows based on criteria to focus on relevant data.

• Exclude irrelevant or unwanted rows.

5. Sorting Rows:

• Order rows based on values in one or more columns to organize the data for better visual analysis.

6. Removing Duplicates:

• Eliminate rows with identical values in all columns to avoid redundancy and potential errors in calculations.

7. Grouping Rows:

• Aggregate data based on common values in a column to summarize and analyze it at a higher level.

• Use operations like sum, count, average, etc., to create summary statistics.

8. Pivoting Columns:

• Reshape the data by rotating columns into rows to create a different view of the information.

• Useful for analyzing relationships between multiple dimensions.

9. Creating Custom Columns:

• Add new columns based on calculations, transformations, or extractions from existing columns.

• Enhance the dataset with additional insights or calculations.

10. Splitting Columns:

• Divide a single column into multiple columns based on delimiters or patterns.

• Break down complex data structures into more manageable parts.

11. Replacing or Filling Values:

• Modify or fill missing values with specific data to address inconsistencies or gaps in the data.

• Ensure data completeness and accuracy.

12. Merging Queries:

• Combine data from multiple sources (e.g., tables, files) into a single query for comprehensive analysis.

• Create unified datasets for multidimensional analysis.

13. Appending Queries:

• Add rows from one query to another to create a larger dataset.

• Expand the scope of the data.

Remember: These techniques are often used in combination to achieve the desired data structure and prepare it for effective analysis and visualization in Power BI.

Name or list a few supported database sources for Power BI ? Power BI interview questions and answers 268

 Name or list a few supported database sources for Power BI ?

Here are some of the supported database sources for Power BI, divided into categories:

Databases:

• Relational Databases:

• SQL Server

• Azure SQL Database

• Access

• Oracle

• IBM Db2

• IBM Informix

• IBM Netezza

• MySQL

• PostgreSQL

• Sybase

• Teradata

• SAP HANA

• SAP Business Warehouse

• Cloud-Based Data Warehouses:

• Amazon Redshift

• Google BigQuery

• Snowflake

• Azure Synapse Analytics

• Multidimensional Databases:

• SQL Server Analysis Services

• Azure Analysis Services

Azure Data Sources:

• Azure Storage:

• Azure Blob Storage

• Azure Table Storage

• Azure Analytics Services:

• Azure Data Lake Storage Gen1 and Gen2

• Azure Data Explorer (Kusto)

• Azure HDInsight (Hadoop, Spark)

• Azure Cost Management

Other Data Sources:

• Files: Excel, CSV, Text/JSON, XML, Folders

• Web: Web Pages, APIs (REST, OData)

• Other: Salesforce, SharePoint Lists, Spark, Databricks, Common Data Service (Microsoft Dataverse), Contoso Data Lake, Dynamics 365 (Finance and Operations), Dynamics 365 Business Central, GitHub, Google Analytics, Google Sheets, MailChimp, Marketo, Planview Enterprise One, Project Online, QuickBooks Online, Salesforce Marketing Cloud, ServiceNow, Smartsheet, SparkPost, Stripe, Twilio, Zendesk, 6 River Systems

Important Notes:

• Connection Methods: Power BI supports two primary connection methods:

• Import: Copies data into Power BI's internal storage.

• DirectQuery: Queries data directly from the source when visualizations are used.

• Gateways: For some on-premises data sources, a Power BI gateway may be required to securely connect.

• Third-Party Connectors: The Power BI marketplace offers additional connectors for various data sources.

Name out some important DAX functions used in Power BI ? Power BI interview questions and answers 267

 
Name out some important DAX functions used in Power BI ?

Power BI utilizes DAX (Data Analysis Expressions) for complex calculations and data analysis. Here are some of the most important DAX functions:

Basic Aggregation:

• SUM: Calculates the sum of values in a column.

• AVERAGE: Calculates the average of values in a column.

• MIN/MAX: Find the minimum or maximum value in a column.

• COUNT: Counts the number of rows in a table or non-blank values in a column.

Filter and Context Control:

• CALCULATE: Modifies the context in which a calculation is performed, allowing for dynamic calculations based on filters or selections.

• FILTER: Creates a new table containing rows that meet specific criteria.

• ALL: Removes all filters applied to a table, bringing back all rows.

• ALLEXCEPT: Removes specific filters from a table while keeping others.

Date and Time Functions:

• DATE: Creates a date value from specific parameters.

• DATEDIFF: Calculates the difference between two dates in specified units.

• CALENDAR: Generates a table with a sequence of dates.

• YEAR/MONTH/WEEK/DAY: Extracts specific components from a date or time value.

Lookup and Relationship Functions:

• RELATED: Retrieves related values from another table based on existing relationships.

• LOOKUP: Searches for a specific value in a column and returns another value from the same row.

• MATCH: Locates the position of a specific value within a column.

Text and Logical Functions:

• CONCATENATE: Combines text from multiple columns into a single string.

• IF: Creates conditional statements based on logic to return different values.

• SWITCH: Evaluates multiple conditions and returns different values based on which condition is met.

Advanced Functions:

• SUMX/AVERAGEX/DISTINCTX: Performs aggregate calculations over rows that meet specific criteria.

• VAR: Defines a temporary variable used within a measure or calculated column.

• GENERATESERIES: Creates a sequence of numbers.

• RANKX: Assigns ranks to rows based on a specified calculation.

This is just a snapshot of some important DAX functions. There are many others available with specific purposes. Depending on your data analysis needs, you can learn and experiment with various functions to unlock the full potential of Power BI.

Remember, the most important functions are the ones that help you solve your specific data analysis problems effectively.

Name some commonly used tasks in the Query Editor.? Power BI interview questions and answers 266

Name some commonly used tasks in the Query Editor.?

Here are some commonly used tasks in the Query Editor, found across various tools like Power BI, Excel, and others:

1. Connecting to Data:

• This is the essential first step in working with data. It involves establishing a connection with various data sources like:

• Excel files

• CSV files

• Databases (SQL Server, MySQL, etc.)

• Web sources (websites, APIs)

• Folders containing multiple files

2. Shaping and Combining Data:

• Filtering and sorting: Selecting specific rows or columns based on criteria, and ordering data based on certain columns.

• Removing or renaming columns: Eliminating unnecessary columns or changing their names for clarity.

• Changing data types: Ensuring columns have the correct data types (e.g., text, numbers, dates) for accurate analysis.

• Merging or appending queries: Combining data from multiple sources into a single query.

3. Transforming Data:

• Grouping rows: Aggregating data based on common values in a column using operations like sum, count, average, etc.

• Pivoting columns: Reshaping the data by rotating columns into rows to create a different view of the information.

• Creating custom columns: Adding new columns based on calculations or transformations of existing columns.

• Splitting columns: Dividing a single column into multiple columns based on delimiters or patterns.

• Replacing or filling values: Modifying or filling missing values with specific data.

4. Refining Data:

• Filtering rows: Selecting specific rows based on criteria.

• Sorting rows: Ordering rows based on values in one or more columns.

• Removing duplicates: Eliminating rows with identical values in all columns.

• Transposing rows and columns: Swapping the orientation of rows and columns.

• Applying conditional formatting: Highlighting data based on certain conditions.

5. Creating Custom Columns:

• Generating new columns using formulas, expressions, or functions to perform calculations, transformations, or extract specific information.

6. Query Formulas:

• Using a language like M or DAX to write custom formulas for advanced transformations and calculations that aren't available in the visual interface.