Today I am gonna share a useful function to create a customized view(personal bookmark)in Power BI. If you are one of the users who access the same dashboard, you may find that sometimes the view is not that fulfill your requirements. However, from the BI engineer’s view, it is not proper to meet each user’s requirement. Here comes the solution for end-users, a customized view. …


Visualization is a trend of data exploration. The key success indicator of visualization is cleaned data sets. To clean data, you can make it through scrips by Python, R, or you can use SQL at the query stage. However, if your role is a BI analyst or end-user, you may have no access right or hard code programming ability, if so, you need to learn how to clean or transform data in your BI tools. We are talking about clean data in Power BI in this article.

Transform function in Power BI desktop version

  1. Duplicate function

You can use this function to duplicate your query. It is…


Digital transformation has been turned into a key objective for many companies and organizations after COVID. The purpose of digitalization is to improve working efficiency and increase resilience to the rapidly changing business environment. I would like to share my experience of preparing and planning digital transformation for a large organization, the topic includes the investigation, the planning, and the execution.

The investigation

The investigation covers the following parts, understand organization culture, identify the politics, understand the duty the teamwork among teams, collect the on-going projects from teams. If you are an external consultant or new hire for a company, you need…


In my previous article, I have a brief introduction of Toyota Business Practice (TBP) which is a very powerful methodology to shape your logic and a great way to solve the problem you encounter. In this article, I will be starting with the target, as we are working to achieving the target no matter what kind of tasks we are taking.

TBP introduction

Root cause finding

As we can use the SMART principle to set our target, we can have an achievable, measurable, and very clear goal over there. The next step is that we need to understand what leads to the problem we are…


Which way is better for you to load data from DB to Power BI

When we create dashboards on the Power BI desktop, most of the time we connect to the database to pull out data. In Power BI, there are two ways you can use to load data from DB, import, or direct query. I will explain the difference and the advantages of each way to let you understand which way is proper for you.

data import options for DB connection

The differences

Import

Using the import function, you can load the data from DB, there is a 1 GB limit to the Desktop if you plan on publishing to the BI Service. …


DAX is widely used in Microsoft solutions such as Power BI and Excel. You may have the experience of using DAX in BI, but in fact, DAX is also a very helpful tool to make the pivot tables even powerful. The foreseeable benefits from DAX are automation, standardization, and easy of maintenance.

In this article, I will be introducing three functions, Sum, Divide, and Calculate function based on conditions, to help you build a powerful and automated pivot table. I use a small data set from Uber which I got from the interview, here is the schema and the first…


Data visualization is quite important for analysts, researchers, or scientists. A few days ago, we saw many visualized charts on news, website, or slides, to emphasize the outbreak and affection of COVID 19. Today, I would like to follow this trend, using John Burn-Murdoch’s chart as a good example and providing you with 3-step tips to make better of your charts.

Source: https://twitter.com/jburnmurdoch

Step 1, Review materials

The material here means your data. There are tons of columns in your data sets and for a chart, you must need to understand what is your topic. In this step, you must decide which information you want to…


At the very first, I would like to explain why the distinct count is so important in data analysis. No matter you are at the stage of data exploration, or you are set the denominators to indicators, it is absolutely important to make a distinct count reflect the reality. Otherwise, you may have over booming indicators or overviews. By means, you can use SQL, Python, R, Pivot, or other tools to do it.

In Python

It is absolutely convenient to use functions in the library. Here it is

table.groupby('your_group_columns').your_target_column.nunique()

In python 3.7, you can also use the function (for list data )


TBP 起源與核心概念

TBP 是Toyota 使用在解決問題的一套方法論,起源於製造業的流程改善與問題解決,而後被歸廣到各行各業。我個人認為,TBP的精隨在於定義問題以及設定正確的目標。定義問題的要點在於拆解問題,也就是把複雜的問題拆分到最小單位,再從最重要或是影響最大的問題開始解決起。設定正確的目標有助於領導我們分配資源,制定計畫,也就是我們要解決這項最重要的問題到什麼程度。問題與目標 對組織橫向來說,每一個產業,每一個部門,都會碰到; 垂直來說,每一個階層也會有需要改善的部分。

TBP 各階段任務與數據分系的關聯

TBP 共分為8個主要的階段,每個階段都有其任務,我提供簡略圖示如下:

TBP

今天這篇文章不會具體闡述每個步驟要做什麼,具體的步驟以及要做的任務會在下幾篇文章中用一個實際的數據分析個案一步步說明,同時融合我這幾年數據分析的經驗,提供讀者在每個階段我們需要注意的事項以及階段任務目標,但如果你迫切想知道前面幾個步驟的要點,可以先參考這篇。今天的文章重點會擺在數據分析如何跟TBP做核心概念上的同步。

在我看來,數據分析有兩種方向的推動(two-direction project driving),一個是透過結果去找原因,比方說透過periodical reports or trend analysis 發現某些異常,進而去探究原因。另外一種是透過分析去找機會,比方說產業分析,新市場分析等等。這兩種方向的分析都需要經過假說與驗證,只是分析的方向、維度與目的大有不同。而TBP更適合用在解決已知問題,也就是透過結果去找原因。

透過結果去找原因的首要任務就是定錨問題,也就是從趨勢中去找到問題點,進而判斷這些問題的重要程度以及影響範圍,然後挑選相對重要急迫的問題來解決,挑選的方式也會在後面文章提到。接著就是設定我們要透過數據分析來改善這個問題到什麼程度,這會大量關聯到我們手邊有什麼資料,要回溯到哪個歷史點,資料的構面要到多少,分析的時間以及實驗的設計有多長多大等等。定錨問題與設定目標後,我們會聚焦在可能的原因,也就是透過數據來找問題點。再來我們透過分析會產出一些解決方向與幾項解決方案,此時也需要透過成本效益分析來評估我們適合採取哪種解決方案。最後則是這樣的Knowhow 要怎麼傳承,這樣的方法論要怎麼expand 到其他問題的解決。 大略介紹了數據分析跟TBP方法論之間的關聯,下面幾篇文章會在帶大家一步步實踐TBP,敬請期待!


This article is writing to the following analyst roles:

  1. You are a green hand in the analytics filed with technical skills
  2. You just transfer to another role and are in charge of a bigger scope (e.g. cross teams, cross countries, cross regions). As we all know the hard skills are basics for an analyst, the communication capabilities and the ability to cooperate across teams are things more important. Now let us start our topics.

To study domain knowledge

Every analytics starts with business understanding which includes product characteristics, life cycles, channels, business models, limitations, etc…We all believe a principle that we can only improve…

Connect business with technique

A business analyst who is familiar with the APAC market and stays with 8-year experience in data analytics, project management, and operations management.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store