이상치 제거 (1) – Power Query

 

이상치 제거 (1) – Power Query

 

Power Query에서 이상치를 제거하기 위한 함수식을 문의하셔서 작업해보았습니다.

Excel에서는 QUARTILE.INC, PERCENTILE.INC 함수를 통해 3사분위수와 1사분위수를 구해 IQR(Q3-Q1)를 생성해낼 수 있습니다. Q3 + 1.5 * IQR 보다 큰 값은 이상치로 판단하는 내용입니다. 아래는 Excel에서 함수식을 통해 SalesAmount 컬럼에서 Q3 + 1.5 * IQR 보다 큰 값을 구해보았고 QUARTILE.INC, PERCENTILE.INC 함수식을 통해 Q3를 확인해보았습니다.

Power Query에서 이상치를 제거하려고 하는 할 때와 차트에서 이상치를 동적으로 제거하려는 경우는 제거하는 시점과 집계에 따라 차이가 납니다.

일단 여기서는 Power Query에서 이상치를 제거해보겠습니다.

데이터를 가져오고 나서 M 수식에서 컬럼 전체 값의 3사분위수를 해당 M 수식에서 구하려고 하니 액세스가 되지 않아 목록을 통해 진행했습니다.

해당 컬럼을 별도 목록으로 만들었습니다. 수식을 적용하여 Q3 + 1.5 * IQR 값을 구하고 M 수식에서 호출할 수 있습니다. 또는 M 수식에서 목록을 참조하여 Q3 + 1.5 * IQR을 구해도 됩니다.

목록을 만드는 방법은 해당 컬럼을 오른쪽 클릭하여 [새 쿼리로 추가]하면 됩니다.

 

해당 컬럼을 가지고 있는 쿼리에서 M 수식을 적용하여 Q3 + 1.5 * (Q3 – Q1)을 구하면 됩니다.

Q3 함수는 List.Percentile 함수를 사용하여 아래처럼 목록을 호출해주면 됩니다.

List.Percentile(upperlimit, 0.75)

 

적용된 결과를 확인해보면 Excel에서 구한 값과 동일한 것을 알 수 있습니다. UpperLimit 열은 Q3 + 1.5 * (Q3 – Q1)를 적용하였고 isOut 열은 SalesAmount와 UpperLimit을 비교하여 필터하기 위한 열입니다. isOut 열을 통해 필터하게 되면 이상치가 제거된 데이터를 로드하여 시각화 할 수 있습니다.

 

 

추가로 SalesAmount의 열 분포를 확인해보면 다음과 같습니다.

 

 

Power Query에서 이상치를 제거해보았습니다.