[ 파워쿼리 ] 전일 대비 증감 구하기

파워쿼리를 이용한 전일대비 증감을 구해 보해록 하겠습니다.

지금은 전일이지만 전월, 전년비 모두 사용 가능합니다.

해당 방법은 열 번호 넣기 & 쿼리 병합을 사용해서 진행 예정입니다.

우선 아래와 같은 데이터를 준비해 주도록 하겠습니다.

 

우선 엑셀로 설명드리면 아래와 같이 11/2일 값을 11/1일과 대비해서 얼마나 증감했는지 알아보는 방법입니다

 

 

해당 데이터를 파워쿼리 창으로 올려 주도록 하겠습니다.

파워쿼리에서는 어떻게 해야 엑셀과 같은 작업이 가능할까요?

 

 

우선 처음에 이야기 한 것처럼 열 번호를 두 번 입력해 보도록 하겠습니다.

[ 파워쿼리 ] 엑셀 열 번호 ( Row number ) 추가

 

 

하나는 0부터 시작하는 열번호( 인덱스 )이고 하나는 1부터 시작하는 열번호( 인덱스 )입니다.

그리고 규칙을 한번 보도록 하겠습니다.

2열 11/2일 자를 보시면 처음 인덱스가 1이고 11/1일 자 인덱스.1 컬럼의 값이 1입니다.

인덱스.1의 값과 일치하는 value를 인덱스로 가지고 오면 전일자 값을 구할 수 있을 것 같습니다.

 

 

쿼리의 data 테이블을 복제해서 previous라는 테이블을 만들어 보겠습니다.

 

 

previous 테이블의 적용된 추가된 인덱스를 삭제하고 이번에는 1번부터 시작하는 인덱스를 추가해 주도록 하겠습니다.

 

 

그리고 data 테이블은 적용된 단계의 마지막 단계를 지워서 아래와 같은 테이블을 만들어 줍니다.

 

 

data 테이블을 선택한 상태에서 [ 홈 ] - [ 쿼리 병합 ]을 눌러 주도록 하겠습니다.

 

창이 나오면 아래와 같이 data 테이블 인덱스 컬럼과 previous 테이블의 인덱스를 선택하고 확인을 눌러 주도록 하겠습니다.

[ 파워쿼리 ] 쿼리 병합 ( Nested Join )

반응형

아래의 내용이 이해가 되지 않는다면 꼭 쿼리 병합을 먼저 읽어보시고 따라오시면 이해가 더욱 쉬울 것입니다.

 

 

 

테이블 병합이 되면 11/2일 자 테이블을 한번 눌러보도록 하겠습니다.

11/1일 자의 값이 들어와 있는 것을 볼 수 있습니다.

data 테이블의 인덱스 1과 일치하는 previous 테이블의 값을 가져오기 때문에 이러한 결과가 도출됩니다.

 

 

 

그럼 previous 컬럼의 확장 버튼을 눌러 value 값만 확장해 주도록 하겠습니다.

 

이제 증감률 계산을 해 보록 하겠습니다.

계산하려고 하는 방법은 아래의 수식과 같습니다.

 ②[ ① ( [ value ] - [ value.1] )  / [ value.1 ] ] 

① 번을 먼저 구해 보도록 하겠습니다.

value 컬럼 & value.1 컬럼을 선택하고 [ 열 추가 ] - [ 빼기 ]를 클릭해 줍니다.

 

그럼 아래와 같이 뺄셈이라는 컬럼에 [ value ] - [ value.1 ]의 결과가 나오게 됩니다.

맞는지 계산기로 한번 숫자를 입력해 보니 맞는 것 같습니다.

 

 

② ① / [ value.1 ]을 수행해 보도록 하겠습니다.

이번에는 value.1 & 뺄셈을 선택하고 나누기를 눌러주도록 하겠습니다.

 

그럼 마지막의 결과가 나오는 것을 볼 수 있습니다.

불필요한 컬럼을 지우고 컬럼명을 변경하고 처음에 엑셀로 구했던 결과와 비교해 보도록 하겠습니다.

 

결과를 보시면 동일하게 나오는 것을 볼 수 있습니다.

※ 복잡하게 진행한 수식을 한번에 진행 ( 사용자 지정 열 추가 ) [ 열 추가 ] - [ 사용자 지정 열 ]을 눌러 보도록 하겠습니다. 아래와 같이 이름을 "증감"으로 설정하고 수식에 다음과 같이 입력해 주고 확인을 눌러 보도록 하겠습니다. 동일한 결과가 나오는 것을 볼 수 있습니다.

---

고급 전처리 방법 ( with M function )

728x90

이번에는 조인 없이 M function을 이용해서 해당 절차를 진행해 보도록 하겠습니다.

원래는 같은 테이블에서 진행하는데 가시성을 위해서 테이블을 2개를 사용하도록 하겠습니다.

이번에는 결괏값을 바로 구하는 것이기 때문에 컬럼명도 바로 바꿔 주었습니다.

우선 previous의 데이터 구조를 보면 11/1일 자의 값은 아래와 같습니다

previous 테이블의 0번째 값이 11/2일 자에 오면 전일자의 값이 구해진다고 볼 수 있습니다.

 

그럼 11/2일 자의 증감 컬럼의 번호는 0번이 와야 연산이 가능할 것 같습니다.

그런데 인덱스는 0이나 1부터 시작하기 때문에 해당 숫자를 구하려면 다른 방법이 필요할 것 같습니다.

 

이번에는 인덱스를 -1부터 시작하도록 설정을 변경하였습니다.

그럼 11/2일자의 증감 값이 "0"이 되고 previous 첫 번째 값을 가지고 올 것 같습니다.

 

다음과 같이 파워 쿼리 M function의 꽃인 Table.TransformColumns 함수를 사용해서 전일자 값을 바로 가져옵니다.

그럼 열추가에서 한 것과 같이 value / 전일 value - 1을 해주야 할 것 같습니다.

 

 

date & value로 Group화를 해 준 후 [value]을 [증감] 값으로 나누어 주는 연산을 진행합니다.

 

 

발생하는 에러를 처리하기 위해서 오류 바꾸기를 클릭해 주고 오류 값을 "0"으로 처리해 줍니다.

엑셀의 iferror와 비슷하다고 생각하시면 됩니다.

 

 

그럼 다음과 같은 결과를 얻을 수 있습니다.