PowerBI stores data in data tables that can be linked, whereas excel stores data in tabular form. |
You can use Power Qurey to ETL your excel data, |
Power query can be added to excel |
You can use power pivot to create a data model- a set of connected data sources, you can add this tool in excel |
In Power BI they use DAX or Data Analysis Expression to create Calculations |
How to directly create pivot charts in excel from the data |
How to enter into the details of the relationship in a powerbi |
One-to-one, many-to-one is called cardinality |
How to create a calculated column in power Bi |
How to remove bottom empty rows |
Creating Calculated Columns in Power BI |
To convert a date into age in Power BI |
Convert a measure from sum to count etc. |
How to change a data from year, quarter to day etc |
Create a grid to snap objects in a dashboard |
DAX can be used to do Period-to-Period Comparison |
ContosoSalesForPowerBI |
I can use all the tables from Dharm as is and open them on the PowerBi |
Choose a new measure such as Sum in DAX ( Sums all into one single value), How to add a measure |
Understanding DAX syntax |
Display the SUM as a total of the components |
For example if you want to choose a particular dollar sign, or decimal places |
Create Measures with COUNT and COUNTROWS |
| Hack |
To write a comment on the measure that you create |
How to move a measure from table to table |
Check your data with COUNTBLANK |
Create a measure with DISTINCTCOUNT eg. how many different stores sold something |
Create a Calculated Column eg. SalesCol - Costcol Should give me profit, that I can use with slicers or filters |
Create a measure with Operators: like Net Sales = Sales -Discount-Return |
Create a measure with Divide eg. Gross profit % = Gross Profit / Sales amount, divide has an ability to resolve divide by zero etc |
An iterator function moves row by row through a table and does a calculation, and then aggregates it, just like aggregation function |
Calculate SumX in each row. Other Iterator functions SUMX, AVERAGEX, COUNTX, MINX, MAXX, RANKX |
How to extract day, month and year from a date, weekday, weeknum, date diff, date at the end of the month, today, now |
Calculate the duration in days of the promotion |
To find a string within a substring eg. , find "mouse" under ProductName, return a new column |
If position of "mouse" is greater than zero, put "yes" else "no", similar to excel, return a new column |
Same as excel OR |
eg. calculate the "measure" only if product manufacturer is "Contoso" and product unit cost is >100 |
How to select and add colors to the visuals |
How to select colors to compare different values |
How to select colors for the color blind |
How to import Custom Visuals |
How to setup your report themes |
How to import your custom report theme into your PowerBI file |
In PowerBI tables are visuals themselves |
Often you can start data analysis by creating tables |
How to create a tabular Pivot Table |
How to add the values to the table |
How to select the comma as the separator for the numbers |
To make it behave like a piovt table with rows, coloumns and values |
To make it from long to wide |
How to sort by a category in the matrix Visuals |
To remove the column totals in matrix visuals when not needed |
How to show empty cells in the chart which do not have values |
To change the color of the separator line in the matrix |
To add a title visual to this matrix |
How to put the title in the middle of the table |
How to change the color of the fonts |
Drilling into Hierarchies |
How to Drill into Hierarchies eg. State--> Counties |
How to drill into hierarchy of a single entity |
To see different levels at the same time |
How to see only values and remove blank from the list in a matrix table |
Only show county names that are not blank |
How to create the copy of the visual |
How to add a filter that only shows the top five |
How to select relative date filtering, eg. in the last 30 days |
How to create Lists |
Set up a list of US states with Highest population |
How to set up multiple state into a group called "Top 5 US states" |
| Creating Bins |
Create the Bins- selecting New Groups Option, rename bins, select bins size |
How to use the bins |
How to use bins as dimensions in the visuals |
How to Keep you DAX measures as a separate tables |
How to create a new measure in DAX |
How to create the value in the Most recent year |
To get the yearly percentage growth |
How to add logo to our dashboard |
How to integrate Logo colors to our dashboard |
How to calculate total passengers by month |
Convert date hierarchy into actual date ( to avoid error message when applying quick measure) |
How to calculate total passengers departing from each US city |
Choose the top 400 US Cities from where the passengers depart |
How to calculate top destination for US |
How to calculate the % of the total passengers departing from US airport |
Rename the head of a table |
Calculation for yoy percentage growth |
Rename a Quick measure |
Create a quick measure calculations for the passengers totals for each year |
Convert a table to matrix visual |
How to add color to the matrix visuals ( Colorful table), as a heat map, red for positive and blue for negative those two are diverging |
How to find top ten state over the last 10 years in the matrix visual |
filter items greater than or equal to a certain value |
Show trends of population ( or any value) over time |
Show Stacked bars as a trend |
Area chart as a trend |
What is ribbon chart how it is used to show the ranking switch |
Using Bar Charts Values over categories |
How to fliter ( drill down) |
How to see the full row label |
How to add data labels to the bar charts |
How to format the data labels |
How to remove the x axis completely |
How to drill down the level of these visuals |
To drill down to the stacked chart to show two levels of hierarchy |
It is better to use bar charts or line charts when we are using high level trends when comparing proportions, for high level you can use tree maps also |
How to compare the top five vs the rest: use 100% column stacked chart |
How to create a Makko Chart |
How to create sparklines |
How to add values to the sparklines |
How to sort the sparklines |
How to filter down the sparklines |
How to put the numbers in bins |
How to create a histogram |
How to set up the custom histogram in the power BI store |
How the counts stack up to the whole |
Distributions by the cateogory themselves in the histogram |
Convert a table to area chart, year by year percentage growth |
How to drill down that chart to yearly passengers numbers |
Most popular destination for travel |
Top US by countries |
KPI help us compare the analyzed metrics against goal metrics such as forecasted numbers or previous numbers within a segment |
How to create the summary card |
Add some formatting to the summary card |
Use the summary card value as a measurement that we can compare the value in a list |
Change the start of y coordinate from some other value to the origin |
To add a trend line to a visual |
How to format the trend line |
Add forecasting as an element to our analysis |
Using forecasting option with the line chart |
How to change the confidence interval to a higher number in forecasting |
How to import box plot from PBI marketplace |
How to use the violin plot |
How to add the categories in the violin plot |
How to format the violin plot |
To analyze the flow of migration between different destinations in the United States |
how to create a scatter plot |
How to add a x axis or y axis constatnt line |
How to add value to card visual field |
Change the unit in the card visual field |
Include a title text on the card visual field |
Add a forecasting line to the line chart |
How to forecast for the next five years |
How to incorporate seasonality in the forecasting |
How to let the model decide about seasonality |
What are the databoard design techniques |
How to remove the granularity of the chart |
How to add a logo and title on the dashboard |
How to add a text box in the report |
How to create a line chart of growth YOY% by year and by region wise split |
Ribbon chart helps to show YOY% growth along with line charts |
Generally these have a Logo, a text box, violin chart by region, Card, Map and a sparkline |
Sparkline visual interacts with the orther visuals in the dashboard |
It contains a logo, A text box, scatterplot, Sankey chart and a map and a slicer |
Add lines or borders to the dashboard to indicate the grouping of two charts |
Format the line or border |
To use a slicer so that it shows only the selected categories |
Normally slicer is convereted into a dropdown menu and put alongside cards |
Modifiying slicers so that at one time only one option can be selected |
Modifying slicers so that multiple selection can be done without using control |
How to have a "Select All" option in the slicer to clear easily any filter |
How to have a search option in the slicer |
To clear the entire slicer |
How to put a box or border around the filter |
How to add a date filter- for time series data |
How to add other options in date filter: before, after , list, dropdown or relative |
To add a filter for a category |
How to format a list in the slicer with horizontal formatting |
How to add "Select all" in the above format |
These are cues to users how to interact or engage |
How to format Sparkline visual with a title |
How to word wrap the title |
How to nudge users to go for drill down arrows |
How to add a nudge on the slicer |
How to give some context to the title of the dashboard |
How to add a text search bar on the dashboard |
How to add search as a functionality as a custom visual from the power BI store |
Search functionality as a wildcard Search, eg. instead of california you can write calif and hit enter |
Search Functionality also acts as a slicer |
Dynamic labels change depending upon what level of data we are analyzing |
How to write DAX Method for creating dynamic labels |
How to add a dynamic Lable through a single card |
We use Spotlight Functionality |
How to use Spotlight Functionality |
How to show the data related to a visual |
How to add other charts as tooltips to our visuals |
How to hide the tooltip page |
How to format slicers which can control whcih visuals will filter |
How to sync slicers which enables us to use one slicer to control and filter other pages. For example if we select " California from High level summary and select it to control other pages in the dashboard |
How to see the slicers across the pages that we are synching |
How to show the filters using images |
How to Import filters that willl show image as the filter |
How to set up Top N ranking or Bottom N ranking based on filters |
How to use the ranking dynamically rather than setting on fliter pane |
How to set up ranking using the DAX measure |
How not to show zeros in the map visual |
How to add a filter that allows users to control the years that they view |
Add a slicer visual for a category, create like a drop down and an option to search the list |
How to filter out the blank rows so that they don't appear in the filter |
How to select the phone layout |
To see the data behind a dashboard and export the data |
How to save the PBI files so that they don't save with the inside data but as template |
How to use the template file |
To save as a pdf file |
Different cloud options to share the dashboards |
How to go to the power query from a desktop |
Comparing Power BI to Tableau |
Ideal Data Table setup for BI is as few columns and as many rows as possible |
What is data folding |
How to upload CSV, excel or txt file |
How to open an excel sheet directly into a power query |
How to rename a sheet |
How to go from the power query sheet to the report editor |
How to bring a table on a website onto powerBI |
Uploading from a folder of Data |
How to run SQL directly in Power Query |
| Import PDF |
Using the first Row as the header |
Renaming the columns |
Removing the columns |
Moving the columns |
Filtering Columns |
If we want to change the data format |
Transposing Data Tables |
If you only want two columns |
In order to see the time series functionality we need to get the time data in the same columns |
How to unpiovt column to convert from wide to long |
How to copy a query and undo the steps to get the original table |
How to append the three tables together instead of uploading them from folders, and when to use it |
Splitting fields and columns |
How to Duplicate Columns |
Fomatting Data with Text Formulas |
Excel vs. power query |
How to Save Year as Date, by concatenation |
How to create a new column |
How to make a column as date |
Using "if" in power query or Conditional formula in power query, Using a column with an "if" formula |
To create a column with the last value of a digit in another column |
Using Conditional column pop up window |
How to fill up null rows in a column with another value in that column |
Fill down populates the null values below the last populated value |
Fill Up populates the null values above the last populated value |
Create a column that measures the rate of inflation between the years |
How to remove other columns except the ones that you select by ctrl +Select |
How to open the M language editor |
Joining Data Tables |
Excel Works in individual Cells, DAX works in columns and tables |
In Excel the formula can apply to one row, but in DAX the formula must apply to all rows |
A DAX MEASURE is a powerful stored formula that is portable |
A Measure doesnt have the same appearance as a calculated column in a data table |
The measure can allow data from different tables |
create a measure Testing = "testing" |
measures can be moved between tables |
The cells are called as "Piovt Coordinates" |
There are "row coordinates" and "column coordinates" |
We cannot reference "Naked Columns" within this function |
What is the average value over single period or an interval |
How to change the location of a measure |
Format of a CALCULATE function with filters |
How to write a CALCULATE function with filters, one of the filter is a date |
How to write a CALCULATE function with filters, one of the filter is a condition |
How to write a CALCULATE function with filters, where multiple filters as AND |
How to write a CALCULATE function with filters, where multiple filters as OR |
What are Threshold filters with AND |
Format of a threshold filter, for example calculate between 0 and 100 |
Removes the filters already applied in a formula |
Format of the ALL functionality |
Needs to be inside another DAX function |
It is used in percentage of a parent or negating a slicer |
How to use the ALL filter |
Types of Filters in Power BI |
Filter is not a standalone function |
Filter Function Format |
When to use the FILTER function |
There are six types of filter conditions |
Why a separate table for measure |
How to create a separate table for measure |
How to create a table using GENERATESERIES function |
format of GENERATESERIES function |
Create a table for 360 monthly period for 30 year loan duration |
Why power funciton |
How to use POWER function into Power BI |
Format of POWER function |
How to use POWER function into Power BI |
Format of the DIVIDE function |
How to use DIVIDE function |
Formula to use for DIVIDE function demonstration |
What is parameter harverster measure, converting a naked column into a measure, you can't use a column directly for calculation like vector in R |
How to create a parameter harvesting measure |
Remaining Loan Balance formula for demonstrataion |
How to calculate remaining loan amount |
Interest Paid and Principal Paid amount formula for demonstration |
How to calculate Interest Paid and Principal Paid in Power BI |
SumX functions to calculate totals over the columns |
These are looping formula that iterate over the table to return the measure result |
Syntax of SUMX function |
How to use SUMX function in power BI |
How to get the running total of the calculations in a column |
How to get the running total of individual compoents of the expression, logic |
How to get the running totals of individual components |
Shortcuts available on powerbi that help us save time |
Useful for year on year changes and running total |
Number of Quick Measures available |
Running totals using quick measures, cumulative frequency |
How to use dates for loan calculations rather than period |
How to create a date table on excel |
How to create a date table using GENERATESERIES |
Format of the Generate Series Formula |
How to create a data table in Power BI |
How to change date format |
How to get the first day of every month |
Filtering between tables |
Format of SELECTVALUE(), establishes relationships between tables |
How to establish a relationship between tables in PowerBI |
Format of Conditional IF Statement |
Format of Conditional IF Statement1 |
Forcasting with PowerBI, Forecast date |
How to forecast in powerBI |
Using IF statement |
Format of the the Dynamic Measure, if crieteria is met select measure 1, except measure 2 |
A slicer is created that works in conjuction with an if statement |
How to create dynamic measure in Power BI |
Format of DATEDIFF function |
Datediff is a measure of interval between two dates using a unit that we specify |
How to use DATEDIFF in powerBI |
Means the tables are intentionally not linked together |
Why Disconnected tables. For example on a given date how many loans are active, rememeber Champo Carpets |
How to work in powerBI |
Example of Blank function |
When blank value is returned it shows up as an empty value |
How to do BLANK in the Power BI |
Writing formula with variables |
Examples of Formula with variables |
How to create a data of fist day of every month |
SumX functionality also works for dates |
DATESBETWEEN format, works like sumX function |
Using DATESBETWEEN in powerBi |
How we can move date pivot coordinates forward one month |
DATEADD as a filter, format |
How to use DATEADD in the powerBI |
DATESQTD and DATESYTD as filter, format |
Using in power BI DATESQTD and DATESYTD |
No comments:
Post a Comment