Power Bi
Why use Power BI?
-Pre-built dashboards and reports for SaaS Solutions -Power BI allows real-time dashboard updates -Offers Secure and reliable connection to your data sources in the cloud or on-premises. -Power BI offers fast deployment, hybrid configuration, and secure environment. -Helps you in data exploration using natural language query
How do you separate currency sign to another column from numbers in power query?
1. In Power Query Editor, select the column that contains the currency values. 2. Right-click the column header and select "Duplicate Column". 3. Rename the new column to "Currency Sign". 4. Click the new column to select it, then click on the "Home" tab and select "Split Column" and then "By Delimiter". 5. In the dialog box, select the first character in the "Delimiter" field, and click OK. 6. The new column will be split into two columns, one containing the currency sign, and the other containing the numbers. 7. You can now remove the numbers column if you want to keep only the currency sign column.
What is a Bookmark?
A bookmark captures the state of a report page. This includes the settings you've made to filters, slicers, and visuals on that page. Once the report page is set the way you want, give it a friendly name. Now you can return to that state of the report page with a single click. Simply select a bookmark, and Power BI takes you back to that view. Types of bookmarks : There are two types of bookmarks Page level bookmarks: & Visual level bookmarks
Why might you have a table in the model without any relationships to other tables?
A disconnected table might be used to present the user with parameter values to be exposed and selected in slicers A disconnected table may also be used as a placeholder for metrics in the user interface.
What are Dimension tables?
A table in the star schema that is used to store attributes and dimensions that describe objects stored in a fact table.
How do you share a power BI report via dashboard with other team members or with the client?
After creating the dashboard in the server level we will get a share option on the top bar of the page just click on that icon and add the email over there with whom you want to share that particular report or you can click on the file option and export the report in power bi or you can click on the file option and export the report in PBIX format or that may be in power bi pdf, print or you can publish it in the web by the URL as well and you can easily share it.
What is Data-Driven Alerts?
Alerts work on data that is refreshed, when data is refreshed by looks for an alert and if it reaches the alert threshold or the limit then the alert will be triggered and notify by email or popup massage./Steps Possible on Power BI Dashboard ONLY. Mange Alert. Add a rule. Give a condition.
What is BI?
Business intelligence is a technology-driven method. It helps you to analyse data and to provide actionable information which helps corporate executives, business managers, and other users to take decisive business decisions
Why should General Formatting be applied to data in Power BI
By formatting data, users can help Power BI categorize and identify data, making it much easier to work with.
What is CORR Function?
CORR is a CORRELATION Function that provides a correlation between two distinct values raging from -1 to 1.
Name some commonly used tasks in the Query Editor
Connect to data • Shape and combine data • Group rows • Pivot columns • Create custom columns
What are the drawbacks of using Power BI?
Dashboards and reports only shared with users having identical email domains. Power Bl does not mix imported data, which is accessed from real-time connections. Power BI can't accept file size larger than 1 GB. Dashboard does not accept or pass user, account, or other entity parameters.
What is DAX Function?
Data Analysis Expressions, is a library of functions and operators it used for creating Formula & Expression.
How to achieve Dynamic Top N Customers?
Dynamic Top N will be achieved with RANKX Function and What-if Parameter.
ETL process
Extracted DATA / transform DATA / load DATA
Difference between Filter & Raw Filter
FILTER Function works on Measure where as Raw Filter works only on Column.
Where is data stored in Power BI?
Fact tables, Dimention tables
What data sources can Power BI connect to?
Files, Content Packs and Connectors
What are the three Edit Interactions options of a visual tile in Power BI Desktop?
Filter, Highlight, None
What is a Full Outter Join?
Full Outer Join: If we want display all the records from both the tables then will use Full Outer Join
How to set alert message in power bi?
Go to my workspace then go to the dashboard that you have created before because alert message can be set on the dashboard only, then click on the three dots on the visual in which chart you want to put the alert message in the dashboard, then click on the manage alert, and add the alert rule and write the message, then give the condition and then add the notification frequency, and give the tick mark on the send me mail two option and after that save and close
Explain the term Custom Visuals.
Graphs or visual which are not included in Power BI desktop are imported for better visualization.
List out benefits of using Variables in DAX.
Here, are benefits of using DAX function: By declaring and evaluating a variable, the variable can be reused multiple times in a DAX expression, which helps you to avoid additional queries of the source database. Variables can make DAX expressions more useful and logical. Variables is only scoped, which should be measure or query which can't be among measures.
What is the use of INDEX Function in Power BI?
INDEX function helps you to retrieve the index of the respective row
How to convert snowflake schema to star schema in Power Bi?
Identify the central fact table in the Snowflake schema, which will become the center of the Star schema. Create dimension tables for each of the associated tables in the Snowflake schema. Remove the redundant data from the dimension tables, keeping only the necessary information for the fact table. Create relationships between the fact table and the dimension tables using primary and foreign keys. Use the Star schema in Power BI to create visualizations and analyze the data.
State the major differences between MAX and MAXA functions
If you want to calculate numeric values, then use MAX. However, if it is for non-numeric values, then you should use MAXA.
Name two types of connectivity modes in Power BI?
Import , Direct Query
What types of data Sources are available in Power BI?
Important Data sources used in Power BI Desktop are: Relational databases: such as SQL Server, Oracle, and MySQL. Cloud-based data sources: such as Azure SQL Database, Azure Data Lake, and Google BigQuery. Big Data sources: such as Hadoop, Spark, and Azure Data Lake Storage. Flat files: such as CSV, Excel, and JSON. Web data sources: such as OData, Web, and SharePoint. Social media data sources: such as Facebook and Twitter. Online services: such as Salesforce, Google Analytics, and Dynamics 365. DirectQuery and Live connection: such as Analysis Services, Tabular model and Power BI data model Power Query: Allows to connect to data sources, reshape and clean the data using a visual interface. Power BI Dataflow: Allows to connect to various data sources, clean and reshape the data, and reuse it across multiple reports. It's also possible to connect to other data sources via custom connectors, which can be created by developers using Power Query and the Power BI API.
What are the advantages of using a variable in Power BI?
Improved readability and maintainability, Increased performance, Greater flexibility, Reusability, better debugging, Better scalability
What is the loading data size difference in Power BI Service Premium & Power BI Service Pro?
In Power BI Service, the Premium and Pro plans have different data size limits. The Premium plan allows for larger datasets and more advanced data modeling capabilities. Specifically, the Premium plan allows for datasets up to 2 GB in size, while the Pro plan allows for datasets up to 1 GB in size. Additionally, the Premium plan allows for more complex data models, such as many-to-many relationships and row-level security, while the Pro plan has more limited capabilities in this regard.
What is Embedded Code?
In Power BI there is an option where we can generate a LINK address for the Power BI report. This link can be shared across to the clint or end users. In a part of the web developer can link that code in their HTML tag.
How can you check the data linkage in query editor in power bi?
In Power BI, you can check the data linkage in the Query Editor by following these steps: Open the Power Query Editor by clicking on the "Edit Queries" button in the Home tab. Once the Query Editor is open, you can view the relationships between tables by selecting the "Manage relationships" option from the Home tab. This will open the Manage Relationships dialog box, which displays a list of all the relationships that have been defined between the tables in the data model. Each relationship shows the name of the tables that are linked, the columns that are used to link the tables, and the type of relationship (e.g. one-to-many, many-to-one, etc.). You can also add new relationships or edit existing relationships by clicking the "New" or "Edit" buttons in the Manage Relationships dialog box. You can also check the data linkage by clicking on the column headers in the table and it will show the table name, column name and its relationships. You can also check the data linkage by clicking on the "View Native Database Query" button in the Home tab, it will show the SQL query that is used to retrieve the data. By using these steps you can check the data linkage in Query editor in Power BI, it will help you to understand the data and how it is related to each other in your data model.
How can you perform Dynamic filtering in Power BI?
In Power BI, you can perform dynamic filtering by using various filtering options such as: Slicers: Allows users to filter the data by selecting different values. Relative date filtering: Allows users to filter data based on a relative date range, such as the last 7 days or next quarter. Advanced filtering: Allows users to filter data based on specific conditions, such as filtering out data where the sales are less than a certain threshold. Drillthrough: Allows users to filter data by drilling down into the details of a specific data point. Selection Pane: Allows users to filter data by selecting or deselecting fields and tables. Bookmark : Allows users to filter data by creating a bookmark of a specific visualization with a set of filter conditions.
Where is incremental licensing refresh feature is accessible?
Incremental refresh feature mainly used for high-end scalability of data by publishing only on that workspace in Power BI Service where Premium capacity is needed.
What is incremental refresh?
Incremental refresh will allow you to refresh only newly added data. Normally when you click on refresh on Power BI will load everything and if you have a big table, it will take longtime, so we do not want that. We just want to load newly added data in the existing data, and how we can do that, we can do that by incremental refresh. You can improve the speed of refresh for long tables by using incremental refresh. This setting will apply once you have published a report to the power BI server.
What are the important features of Power BI dashboard?
It allows you to drill through the page, bookmarks, and selection pane. It allows you to create various tiles like web content, images, textbox, and integrate URLs. Allows you to set report layout to mobile view.
How to group different columns in dataset?
It can be achieved using DAX function like SUMMARIZE ( ), GROUPBY ( ).
What is the calculated column?
It is a calculating expression of DAX which allows calculation on data stored in a new column.
What is Data source filter?
It is a parameter to filter the data before loading it into the machine.
What is DAX?
It is formula language used to compute calculated column and calculated field.
What is Filter function in Power BI?
It's a function which allow you to manipulate data to create Dynamic Calculation.
What do you use as a consolidate inquiry in Power BI?
Join Queries are used as a consolidate inquiries in Power BI
What is the main difference between LTRIM and RTRIM?
LTRIM function helps you to remove the white space from the LEFT of the string. RTRIM helps you to remove it from the right the last index.
What is a Left Anti Join
Left Anti: Displays only the mismatched records from Left Table
What is a Left outter join?
Left Outer Join: If we want all the records from left table and only matching records from right table then will use left Outer join/left join
Define the term "M language"?
M language is a mashup query language for Power Query. it is a programming language used in Power Query. It's a functional, case-sensitive language.
What is DAX most common Functions?
Maths & Stats, Logical, Text, Filter, Date / Time Intelligence
Can I export the power BI report data into any other formula like SSRS ( Word, Excel, PDF, CSV)?
NO you can't export data of entire page at a time. But YES you can export the data of single visualization at a time.
What are the data types of Dax?
Numeric • Boolean • DateTime • String • Decimal
Explain the term responsive slicers.
On a report page, you can easily resize a responsive slicer to various sizes and shapes, and the data contained in it should be rearranged according to it. In case if the visual becomes too small to be useful, an icon representing the visual ability to take its place, thus saving the space on the report page.
What is On-Premise Gateway?
On-Premise gateway acts as a bridge which helps you to transfer the data, which is on-premise safely and securely to the cloud environment.
What are the different types of refreshing data for our published reports?
Package refresh, Model/data refresh, Tile refresh, Visual container refresh
What is Power BI Desktop?
Power BI Desktop is a free desktop application that can be installed right on your own computer. Power BI Desktop works cohesively with the Power BI service by providing advanced data exploration, shaping, modeling, and creating report with highly interactive visualizations.
Name out three important Power BI tools?
Power BI Desktop—It is used for desktop computers Power BI service—It is an online software as a service tool Mobile Power BI apps—They are used for iOS and Android devices.
What is Power BI service?
Power BI Service is a cloud based service which help us to publish the Power BI report and share it with client.
How is data security implemented in Power BI ?
Power BI can apply Row Level Security roles to models.
Explain the difference between Power BI and Tableau?
Power BI focusses only on modeling and reporting, while Tableau is the best tool according to data visualization. Power BI is less expensive, while Tableau is very expensive. Power BI is associated with Microsoft Azure, while Tableau uses python Machine learning. Power BI has a simple and easy to use interface while Tableau has a customized dashboard. Power BI becomes slow while handling massive amounts of data, while Tableau easily handles any amount of data.
What is power bi
Power BI is a Business Intelligence and Data Analytics Visualization platform provided by Microsoft
Who mainly uses Power BI?
Power BI is mainly used by: PMO - Project Management Office Business & Data Analyst Developer & Database Administrator IT Team, IT Professional Consumer for End User Report Data Scientist
What information is required to create a map in Power Map?
Power Map can display visualizations which are geographical in nature. That's why some kind of location data is needed, for example, city, state, country or latitude and longitude.
What is Power Pivot, and what are the filter writes in Power BI?
Power Pivot is an in-memory component which offers highly compressed data storage. It helps you to build a data model, creating formulas, relationship, calculated columns from various resources. Filters are applicable in: Visualization level Filter. Page Level Filter. Report Level Filter.
What are the important components of the Power BI toolkit, and what do they do?
Power Query -ETL tool, Power Pivot: A modeling tool, Power View: It is a presentation - Power Map: Helps you to create geospatial representations of your data. Power Q&A: Allows you to use natural language to get answers to questions
What is the different between of Power Query & Power Pivot
Power Query is Used to clean data Use M function, Required more memory for M function. Power Pivot is used for data modeling , Use DAX function, Lightweight function does not occupy more space
What is Power View?
Power View is a data visualization technology that lets you create interactive charts, graphs, maps, and other visuals which bring your data to life. Power View is available in Excel, SharePoint, SQL Server, and Power BI.
How can I speed up Power Query in Power BI?
Remove redundant columns-Database normalization-Use imported columns and avoid calculated columns- Minimize iterator functions-Query reduction-Performance analyzer-External Power BI tools
What is a Right Anti Join
Right Anti: Displays only the mismatched records from the Right Table.
What is a Right Outter Join?
Right Outer Join: If we want to display all the records from right table and only matching records from left table then will Right Outer join/Right Join.
What is Row-Level Security in Power BI?
Row-Level security restricts the user to view and access the data based on filters [ finance Dep.- HR dep.] can view the data related to their department. However, to enable Row-Level security, a Power BI account PRO subscription is essential.
What is Implemented row level security in Power Bi?
Row-level security (RLS) in Power BI allows you to restrict data access at the row level based on user roles.
Difference between Sum & Sumx?
SUM works on a Single Column vs SUMX can take the Table as Input SUMX has row-context whereas SUM doesn't have SUMX is an iterative Function whereas SUM is not.
Why use selection pane in Power BI?
Selection Pane helps you to take control over visuals which require to be displayed and which should not be displayed. It allows you to combine multiple visual pages in the group and is also used in bookmarking.
Explain the process of dynamic filtering?
Set all the data of a report ready to be published Publish it into group workspace Create a filter link Create a DAX calculated column Public overview report
After you publish the report, in how many ways you can share with end users in Power Bi?
Share via Power BI Service: Share a link to the report or dashboard in the Power BI service Share a link to the report or dashboard with specific people, groups, or even entire organizations Share a view-only or interactive version of the report or dashboard Share a report or dashboard in a workspace, where multiple users can collaborate on a set of related reports and dashboards Share via Email: Send a link to the report via email Email a PDF, PowerPoint, or image of the report Schedule an email delivery of a report or dashboard on a recurring basis Share via Embed: Embed a report or dashboard in a website or application using the Power BI Embedded service Embed a report or dashboard in SharePoint Online or on-premises using the Power BI Web Part Embed a report or dashboard in Teams using the Power BI Tab Share via API: Use the Power BI REST API to programmatically access and manipulate reports and dashboards Use the Power BI SDK to create custom applications that integrate with Power BI Share via Mobile: Share a link to the report or dashboard in the Power BI mobile app Share a view-only or interactive version of the report or dashboard in the mobile app. Send a push notification to the mobile app when a report or dashboard is updated.
What is Slicer?
Slicer is One-Canvas visual filter in power BI, which allow users to filter the data by clicking on the type of data you want.
What is the use of Split function in power bi?
Split Function is a part of Power Query editor and it is useful to split data into different columns based on delimiter, number of characters and by position. A nice example is shown below where one column data is split into multiple columns based on delimiter.
What is the different between star & Snowflake Schema
Star Schema-A central fact table and multiple dimension tables, Fewer tables and simpler relationships, Optimized for reporting and querying performance, Denormalized data model, Less storage space required, Easy to understand and navigate, Suitable for large data sets Snowflake Schema-A central fact table, dimension tables, and sub-dimension tables, More tables and complex relationships, Optimized for data storage and normalizationm, Normalized data model, More storage space required, More complex to understand and navigate, Suitable for smaller data sets
What is USERELATIONSHIP() DAX function?
The USERELATIONSHIP() DAX function in Power BI is used to specify the relationship that should be used in a calculation.
Append Query vs. Merge Query in Power BI?
The append query is used to append the data of two or more queries/tables with the same structure or a different structure into a single query/single table. Merge query is used to merge only two queries/tables into a single query/table with a common column condition. Both sides of tables must have common column so that using this common column (Like a join) we merge data.
What is Cardinality?
The cardinality of the relationship means having unique or multiple instances per value for the joining field between two tables. Many to One - One to Many - Many to Many
What are Fact tables?
The central table in a star schema of a data warehouse, a fact table stores quantitative information for analysis and is not normalized in most cases.
What is natural language search choice?
The natural language search choice is language-based learning for communicating with information as a part of Power BI suite for Office 365 advertising.
State the main difference between Distinct() and Values() in DAX?
The only difference between two functions is that with Distinct help you to calculate the null values
What is the only prerequisite for connecting to a database in Azure SQL Database?
The only prerequisite for before connecting to a database is that the user need to configure firewall setting to allows remote connections.
What is query collapsing?
The process of converting the steps in power query editor to SQL and executing it by the source database is called query collapsing Query collapsing is a part of the query folding and folding process, which is the process of translating the query into the native query language of the data source. Query collapsing can be enabled or disabled in Power BI Desktop settings, depending on user's preference.
How to get data from SQL?
The syntax to get data from a SQL Server to Power BI using the built-in "SQL Server" connector is as follows: 1- In Power BI Desktop, click on "Get Data" and select "SQL Server" from the menu. 2- In the "SQL Server" connection window, enter the server name and database name, and select the appropriate authentication method (Windows or Database). 3- Once connected, you can either select specific tables or views from the database to import, or you can write a custom SQL query to retrieve specific data. 4- Click on "OK" to load the data from SQL Server into Power BI. Note: If you are using a DirectQuery for your data source, the syntax would be different. let Source = Sql.Database("Server", "Database", [Query="SELECT * FROM Table"]) In Source
What are the different types of roles in Power BI?
There are four roles in Power BI workspace, Admin, Member, Contributor, and Viewer.
What is content packs?
These are pre-built solutions build for popular services as a major part of the Power BI experience it is like a zip file, it can have a dashboard, reports and relevant database which can shared across the clint.
How to keep or remove specific rows in power bi?
This feature is present in Power Query Editor and this feature allows us to keep or remove specific rows in a table. We can keep or remove top, bottom, range of rows not only row data but also, we can remove errors and duplicates, very important feature with respect to data transformation.
What are Tiles in Power BI?
Tile is an important feature of power BI services and can be said as a picture of the data which can be pinned to the dashboard. Tiles in Power BI are small, rectangular boxes that display a single value or a small set of values from a data source. Tiles can be used to display key performance indicators (KPIs), such as sales totals or customer counts, or to show other types of data, such as a chart or an image. Tiles can be placed on a dashboard and can be formatted to display different types of data and to show or hide different elements, such as data labels or legends. They are used to give a quick glance over the important data points, and to make the dashboard more interactive and user-friendly. Tiles can be created using various visualizations such as Card, KPI [ key performance indicators], gauge, etc. Tiles can be connected to a single data point or multiple data points to show the data in different ways. They can also be linked to other pages or reports in Power BI, allowing users to drill down into the details of a specific data point.
How can u download excel files which has millions of records from a Power BI report?
To download large extracts of data, we can host the excel file to a share point, which will generate a .xlsx link, which can be embedded to "Text" or "Button". On clicking on the Text link or Button. The excel file gets downloaded directly which is hosted in share point or some other base location
What is the difference between visual level, page level and report level filters in power bi?
Visual level filter will able to filter selected visualization only Page level filter will able to filter all visuals present in a single page Report level filter will able to filter all visuals present current page and also present all current pbix report.
What are the types of filters available in Power BI Reports?
Visual-level Filter -Page-level Filter - Report-level Filters
What are Building Blocks in Power BI?
Visualizations, Datasets, Reports, Dashboards, Tiles
What is the IF Parameter in Power BI?
When we want to see the visualization base on a scenario, the best way is to use the what-if parameter. it could help with forecasting data and doing advance analytics
What is query Folding?
With query folding, Power Query will optimize data retrieval according to how the data is being used later.
Can you join two unique information source in the same Power BI dashboard?
Yes, we can join two unique information source in the same Power Bi dashboard. We can also merge comparable reports in your Power Bi account into using single dashboard.
How to handle Many to Many relationships in Power BI?
You can use Cross filtering option in Power BI to address the Many to Many relationships, Also we can create a new table as a bridge to link the two other tables in your data model and create the necessary visualizations.
Explain z-order in Power BI
Z-order is a design strategy which is used for arranging visual over shapes. It can be defined as an implementation method which can be applied whenever reports have multiple elements.
What are the diffrent components of power bi
power query, power pivot, power view, power bi desktop, power bi map , power bi service
Do we need data gateway for schedule refresh in Power bi?
yes, if your data lies on on-premises data sources.... if your data is somewhere in azure or azure sql server, one drive or SharePoint then we don't need data gateways.