Friday 5 January 2024

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:


  • Relational Databases:

  • SQL Server

  • Azure SQL Database

  • Access

  • Oracle

  • IBM Db2

  • IBM Informix

  • IBM Netezza

  • MySQL

  • PostgreSQL

  • Sybase

  • Teradata


  • 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.