Mistakes are inevitable while learning a new tool. After all, that’s how you learn it. There is no
learning without putting it into practice. But if you are a novice & are trying to deliver a
Powerbi
dashboard in the form of a report to the client, the following will save you a bunch of
time.
The detect data type is a great feature, however, it does not always classify your data based on
the context & you will always have to validate it. Let’s just say you have a table with two
columns: Different pin codes & countries that the pins belong to. If you detect the data type of
this table, the pin codes would be classified as whole numbers & countries as text. All good,
right? Not really.
Best practice:
While it’s all right to classify ‘countries’ as text, it’s important to go to the data view &
under column tools, make sure you classify this as a “Country” in the data category dropdown,
again “Country” and not “County”.
Let’s say we have two tables with different information, but the data transformation steps
applied are the same on both tables. The only thing that isn’t the same is the table name, so
only one filter must be applied differently. Let’s call the first table ‘seasonal purchases’ &
the other ‘non-seasonal purchases’. You have applied all the transformation-related steps & data
filters on the seasonal purchases table. Now that you know what steps need to be applied on the
non-seasonal purchases table, you go ahead & manually try to do the same operation on it, just
by using a different filter while navigating to the table & giving the filter condition
“contains non”. While this isn’t wrong, it’s laborious & takes time.
Best Practise:
Under the Home section of the power query editor, there is an option named “advanced editor”,
click on it & copy the query for the seasonal purchases table. This has all the operations &
steps applied on table 1, now select the non-seasonal purchases table, go to the advanced editor
& paste the query here. Voila! You have the same data transformation steps applied on this
table, now navigate to the step where the table name filter is applied & change the filtering
condition & you are good to go.
In the initial phases, when we try to use multiple tables in a power bi report, we perform
calculations on individual tables & lookup data from any other table if required, while this
might not throw an error, but there are chances the resultant calculations aren’t always
appropriate.
Best Practise:
It’s crucial to establish relationships between tables & have a robust data model. A complex
data model might not always be the best, but it’s always recommended to relate the table based
on primary key – foreign key relationship to accurately calculate results & have a correct
information in the report. Consider that we have two tables in a report: the first table is the
customer master that has customer-related details along with product codes purchased by the
customers, here the primary key is billing code or billing number. The second table is the
product master, where the primary key is the product code. By navigating to the model section of
the dashboard & clicking on the “manage relationships” option on the top, we can establish a
many-to-one relationship between the customer master & the product master. Product code is a
foreign key in the customer master table, so it might have multiple instances of the same code,
but since it’s a primary key in the product master, each code will have only one instance (no
duplication) & hence the nature of the relationship being “many-to-one”.
Imagine that you are inputting data for your dashboard from the ADLS (Azure Data Lake Store) gen
2, the link to the ADLS is something of this sort: https://adlstest1.dfs.core.windows.net/
You input this in the source & put it in the secret key, then filter out the folder in the data
transformation steps, based on where your file is located. However, every time you make a change
in the data in the power query editor & close & apply the change, it takes 30 minutes or more to
reflect that change & refresh data, you wonder why. This is because power bi is having to run
across the whole storage & find the folder that has the required data.
Best Practise:
Here, it’s important to get the exact path of the blob storage where your file is located & put
that into the data source section. Rather than putting the path of the analytics workload space,
search where your file is in ADLS, copy the folder path: https://adlstest1.blob.core.windows.net/Purchases/Nonseasonal/2020/
Input this as the source & notice the refresh taking 1/4th the amount of time it previously
took. This is because the folder path where the table is, is directly given & powerbi doesn’t
have to run through multiple filtering to get there.
Ever wondered why the Facebook logo is blue? Not the bright blue but the calm, subtle sky blue?
This is because blue is a color that’s very close to neutral & it imparts a sense of trust
amongst digital users. Initially, we are tempted towards using brighter colors to highlight the
key components & use bold font. We assume it makes things clearer & more noticeable. While it
does make things noticeable, it’s a bit harsh on the eyes & might not be the best in every
situation.
Best Practise:
Use color palettes, it will reduce the number of contrasting colors on the powerbi dashboard.
Specify legends on the dashboard, so that the user is clear on what color signifies what
category. Make sure the legends are in proper font style & readable font size.
I hope these learnings help you the next time you develop a powerbi report.
