DAX와 파워비아이

파워쿼리에는 간단히 버튼 하나만 클릭하면 복잡한 수식을 입력하는 것과 똑같은 효과를 볼 수 있는 명령이 있는데요.

그 중 대표적인 것이 그룹화입니다.

엑셀을 써보신 분들은 조건에 맞는 합이나 행의 개수를 파악하기 위해 SUMIF, SUMIFS, COUNTIF, COUNTIFS같은 수식을 수시로 썼을 거라 생각합니다.

처음에는 복잡해 보이지만 수식을 이해하면 이처럼 편한 수식이 없죠.

아마 엑셀 수식 중 가장 많이 사용되는 수식중 하나가 아닐까 합니다.

DAX에서 FILTER를 이용해 구할 수 있지만, 만일 쿼리 에디터에서 작업을 해도 된다면 아주 쉽게 해결할 수 있습니다.

그룹화는 무엇인가?

그룹화는 위에 설명해 드린 데로 열의 조건에 따라 원하는 열의 데이터를 연산하는 것입니다. 조건이 몇 개든 요약하고자 하는 연산이 몇 개건 상관없이 한 번의 명령으로 실행되는 것이 장점이며, 아래에 보시는 것과 같이 합계, 평균 등 총 8개의 연산을 제공합니다.

그룹화 사용 방법

먼저 그룹화는 홈 탭을 통해 실행할 수 있으며, 명령 실행 후에는 위에서 보는 것과 같이 새 창이 나옵니다.

기본은 하나의 열을 조건으로 한 하나의 연산을 실행하는 것이고, 고급을 누르면 SUMIFS나 COUNTIFS와 같이 복수의 조건을 만들 수 있습니다.

그리고 집계 추가 버튼도 나오는 데 이를 이용해 추가 연산도 가능하게 됩니다.

만일 그룹화 명령을 실행하기 전에 복수의 열을 선택하면 바로 고급 옵션으로 들어가게 됩니다.

그룹화의 예

만일 아래의 표에서 Brand과 Type을 그룹으로 하여  총 판매량과 평균 리콜 비율을 알고 싶다면 Brand와 Type 두 열을 선택하고 그룹화를 누릅니다. 그다음 새 열 이름을 넣고 연산과 열을 선택하면 됩니다. 여기서는 연산을 합계, 평균 그리고 열은 Year와 Recall을 선택하면 되겠죠.

그룹화의 단점

그룹화를 실행하게 된다면 일단 쿼리내의 테이블이 그룹화된 형태로 바뀌게 됩니다. 이 말은 기존 데이터는 더 이상 볼 수 없다는 것을 의미합니다.

만약 데이터의 최종 형태가 그룹화라면 크게 상관없지만 그렇지 않다면 기존 데이터를 사용할 수 있는 다른 방법을 찾아봐야 합니다.

만약 데이터를 없애지 않고 계속 두고 싶다면

테이블 형태가 그룹화가 최종이 아니라 데이터를 이용해 전처리 작업을 더 진행하고 싶은 경우는 2가지 방법을 고려할 수 있습니다.

• 기존 쿼리를 복사해 데이터를 그대로 두고 그룹화 명령을 실행한 뒤, 다시 기존 테이블로 돌아와  다른 작업을 진행
• DAX에서 FILTER 명령으로 똑같은 효과를 얻을 수 있으므로, 쿼리 에디터를 빠져나와 측정값이나 데이터 보기 창에서 CALCULATE와 FILTER를 이용하여 함수를 만든다.

이상 그룹화에 대해 말씀드렸는데요, 쿼리 에디터 내에서는 복잡한 수식 없이 간단한 방법으로 조건에 따라 연산을 수행할 수 있으니 목적에 맞게 잘 사용하면 좋은 명령이라 생각합니다.

파워 쿼리에서 작업하면, 데이터를 목적에 맞게 나누어야 할 때가 많습니다.

예를 들어 텍스트가 너무 길다거나, 날짜 데이터에서 연도, 월을 따로 구분한다거나, 고객 명단에서 성과 이름을 각각 다른 열에 저장하고 싶을 때 버튼 하나로 아주 간단히 해결할 수 있습니다.

열 분할 이란?

열 안에 있는 문자나 숫자 등을 필요한 만큼 자르거나 나누는 기능입니다.

홈 혹은 변환 탭 안에서 찾을 수 있는데, 열 분할 버튼을 누르면 총 7가지 선택사항이 나오는데, 여기서 가장 많이 사용하는 구분 구호 기준, 문자 수 기준에 대해 말씀드리겠습니다.

구분 구호 기준

Date 열을 보면 연-월-일로 정리가 되어있습니다.

여기서 똑같이 반복되는 기호가 -이라는 건 금방 알 수 있습니다.

여기서 연, 월, 일을 각각 하나의 열로 만들고 싶다면 -를 기준으로 데이터를 정리하면 쉽게 만들 수 있겠죠.

열 분할에서 구분 구호 기준을 선택하면 창이 나오고, 구분 기호 선택 또는 입력란에 파워비아이가 구분 기호를 자동으로 인식해서 열 분할 준비를 합니다. 만일 다른 기호를 이용하고 싶다면 메뉴에서 선택하든지 아니면 자유롭게 입력할 수 있습니다.

여기서는 파워 쿼리가 자동으로 - 를 구분 기호로 인식하고 있네요.

여기서 어떻게 분할할지 선택하면 됩니다

• 맨 왼쪽 구분 기호에서 - 구분 기호에서 맨 왼쪽 하나만 분할
• 맨 오른쪽 구분 기호에서 - 구분 기호에서 맨 오른쪽 하나만 분할
• 각 구분 기호에서 - 모든 구분 기호에서 분할

선택한 뒤 확인을 누르면 아래와 같이 연, 월, 일의 열이 자동으로 생성됩니다.

파워쿼리에서 작업을 하면 명령을 실행할 때마다 자동으로 속성이 지정되는 걸 볼 수 있는데, 여기도 예외는 아니네요.

만일 연, 월, 일 데이터를 문자 속성으로 사용하고 싶다면, 옆의 적용된 창에서 마지막 단계를 없애면 쉽게 해결할 수 있습니다.

문자 수 기준으로 데이터 분할하기

아래의 Month Name이라는 열에서 달 이름이 너무 길어서 Jan, Feb, Mar과 같이 3자로만 표현하고 싶다면 이 기능을 이용하면 됩니다.

문자 수 기준을 선택한 뒤 창이 나오면 원하는 길이를 입력합니다.

• 가능한 한 왼쪽 끝에서 한 번  - 입력한 숫자만큼 왼쪽에서부터 적용
• 가능한 한 오른쪽 끝에서 한 번 - 입력한 숫자만큼 오른쪽부터 적용
• 반복적으로 - 왼쪽부터 입력한 숫자만큼 계속 반복해서 길이를 나눔

아래의 테이블에서 달 이름이 너무 길어서 Jan처럼 3자로 나타내고 싶은 것이니 문자수에 3을 입력하고 가능한 한 왼쪽 끝에서 한 번 을 선택하고 확인을 누르면 다음과 같은 결과를 얻을 수 있습니다.

나머지 명령도 제목으로 쉽게 짐작할 수 있으니, 상황에 맞게 사용하시길 바랍니다.

눈에 보이지 않는 데이터의 공백

데이터 전처리에서 눈에 보이지 않는 요소로 인해서 많은 양의 데이터가 누락되는 경우가 많습니다

아래의 예를 한번 보시면 금방 이해가 될 거로 생각합니다.

네 개의 데이터가 모두 같아 보이지만 실제로 파워 쿼리에서는 4개의 각각 다른 데이터로 인식합니다.

세 번째는 차이가 느껴지죠? Power 앞에 공백 하나를 가지고 있습니다.

이 공백 하나 차이로 세 번째 데이터는 다른 데이터로 인식됩니다.

그럼, 나머지는 뭐가 차이가 있을까요?

첫 번째는 아무 글 앞뒤로 아무 공백이 없으며, 두 번째는 마지막 BI 뒤에 공백 하나, 네 번째는 BI 뒤에 공백 2개를 가지고 있습니다.

이러한 차이로 인해 파워 쿼리에서는 상이 값 4개, 고유 값 4개로 인식합니다.

만일 이런 차이점을 무시하고 데이터 전처리를 진행한다면, 기대했던 결과치를 얻을 수 없게 되는것입니다.

Power BI라는 문자와 연결되어야 할 데이터가 결국은 하나로 다 합산이 되지 않고, 결국에는 4개로 분산되기 때문이죠.

그럼 이런 공백은 어떻게 지울 수 있을까요?

공백 제거 (Trim) 를 활용하자

이를 없애는 간단한 도구가 공백 제거 (Trim)입니다.

실행하는 방법은 2가지가 있는데, 하나는 변환 리본에서 서식을 선택하면 공백 제거가 나오고요, 다른 하나는 머리글에 커서를 놓고 마우스 오른쪽 버튼을 누르면 메뉴가 나오는데, 여기서 변환을 선택하면 공백 제거라는 명령을 볼 수 있습니다.

공백 제거 명령을 실행하면 글자 전후로 들어있는 모든 공백을 없애주고, 열 분포를 다시 확인하면 기존 4종류였던 데이터 개수가 1개로 되었음을 알 수 있습니다.

그럼, 정리 (Clean)는 무엇인가?

정리 (Clean) 명령은 공백 제거 (Trim)처럼 많이 사용되지 않습니다.

데이터 보관을 가장 작은 형태로 만들어둔 기존 데이터를 파워 쿼리로 가져오는 경우는 정리 (Clean) 명령을 사용할 일이 거의 사용할 일이 없기 때문이죠 

하지만, 간혹 엑셀에서 데이터를 가져올 때 비록 데이터가 한 셀에 들어 있지만 아래와 같이 두 줄로 입력된 경우를 종종 발견할 수 있습니다.

이럴 때 정리 명령을 사용하면 데이터를 아주 쉽게 변환할 수 있습니다.

공백 제거 (Trim)은 앞뒤 공백만 제거해 주는 반면 정리 (Clean)은 두 줄로 된 데이터가 한 줄로 정리됨을 확인할 수 있습니다.

데이터를 분석한다는 것은 올바른 데이터를 가지고 있어야 제대로 된 분석이 가능합니다. 데이터 분석을 진행하기 전, 문자의 속성을 가진 열은 약간은 번거로운 과정을 거치는 것이 전처리 과정 중 오류를 최소화하는 것은 물론, 올바른 결과치를 얻을 수 있기에 데이터 분석을 시작하기 전 데이터를 정리하는 습관을 지니는 것이 좋습니다.

열 이름으로 사용될 행을 머리글로 지정했다면 다음을 가장 먼저 확인해야 합니다.

• 열 속성
• 오류 확인

속성은 아래와 같이 크게 5가지로 구분되나 숫자, 날짜, 문자가 가장 많이 사용되며,  숫자, 날짜는 좀 더 세분화 됩니다.

• 숫자
• 날짜
• 문자
• 참/거짓
• 이진법

열 속성 지정

처음 데이터를 가져오면 모든 열 속성이 문자와 숫자가 동시에 보이는데 이는 열 속성 지정이 되지 않았음을 의미합니다.

열 속성을 나타내는 곳을 클릭하면 메뉴가 나오는데 이 중 하나를 선택하면 속성이 바뀌는 것을 확인할 수 있습니다.

주의할 점

열 속성이 지정되지 않아도 작업 진행에는 문제가 없습니다. 하지만, 첫 번째 Segmant 열을 예로 들면 화면상에서는 모두 문자를 보여주고 있지만, 아래 보이지 않는 곳에 수많은 행이 있는데 그 중 숫자나 날짜 같은 것이 섞여 있으면 데이터 전처리 과정에서 오류가 나올 수 있으므로 번거롭겠지만 처음부터 속성을 지정하고 시작하는 습관을 들이는 것이 좋습니다.

오류 확인

아래와 같이 숫자로 이루어진 열에 실수로 문자가 들어갔을 경우 속성을 숫자로 바꾸면 오류가 있다는 것을 한눈에 알기 쉽게 표시하며, 문자가 들어간 행에 에러가 있다고 보여줍니다.

데이터양이 작다면 쉽게 파악이 되나 수많은 데이터를 다루게 된다면, 아주 유용하게 사용됩니다.

이런 경우는 원본 데이터를 수정하거나, 아니면 에러가 있는 행을 삭제하고 데이터 전처리를 진행하면 문제가 없습니다.

열 이름 바꾸기기

혹시 열 이름을 임의대로 바꾸고 싶다면 열 이름을 더블클릭하고 수정하면 됩니다.

Segment라는 열 이름을 Category라고 바꾸고 싶다면 Segment를 더블클릭한 뒤 Category라고 입력하면 열 이름이 바뀌고, 지금껏 작업한 곳에 열 이름이 자동으로 수정되기 때문에 데이터 변환 중 바뀌더라도 큰 문제가 없습니다.

숫자열과 문자열의 구분

한가지 기억해두시면 좋은 것은 문자열과 숫자열의 구분인데 간단한 구분법은 아래와 같습니다.

• 문자열: 좌측정렬
• 숫자열: 우측정렬

지금 Sale Price는 숫자의 속성을 가지고 있으므로 우측정렬이 되어있지만, 속성을 문자로 바꾼다면 아래와 같이 좌측정렬을 하게 됩니다.

열 이름에 있는 속성을 하나하나 확인하지 않더라도 어떤 속성을 가지는지 쉽게 알아볼 수 있습니다.

파워비아이 쿼리 에디터로 데이터를 가져오면 가장 먼저 해야 할 것이 열 이름을 지정하는 것입니다.

쿼리는 열 이름을 사용하여 모든 명령을 실행하기 때문에 이를 지정하는 것이 쿼리 에디터에서 데이터 분석을 시작하는 첫걸음입니다.

파워비아이에서 엑셀 파일로부터 데이터를 가져올 때 아래와 같은 경우가 많습니다.

때로는 자동으로 인식해서 열 이름을 알아서 지정하는 때도 있지만 아래의 경우를 볼 수 있습니다.

최상의 행이 열 이름으로 지정되며, 보시는 것과 같이 회색 바탕을 두고 있습니다.

현재 열 이름은 Column1, Column2와 같은 형태로 되어있으며, 엄연하게 열 이름을 가지고 있으니 현재 상태로도 Column1, Column2를 열 이름으로 하여 수식을 만들거나 편집하는 데는 아무 문제가 없습니다.

별문제가 없어 보이지만, 마지막 5, 6번째 열에서 숫자와 문자가 섞여 있으므로 작업을 진행할 때 100% 오류가 나올 수 있는 형태의 데이터 테이블입니다.

보기의 열 분포를 선택하면 다른 열과는 다르게 아무것도 표시하지 않는 것을 볼 수 있는데요, 이는 열 구성이 뭔가 이상하다는 것을 단편적으로 보여주는 것이기도 합니다.

여기서 오류가 없는 테이블을 만들려면 2가지 방법이 있는데요, 하나는 첫 번째 열을 지우거나, 다른 하나는 첫 번째 열을 열 이름으로 만드는 것입니다.

변환 > 첫 행을 머리글로 사용 버튼을 이용하면 되는데요.

사용하는 법은 아주 간단한데, 변환 > 첫 행을 머리글로 사용을 누르면 첫 행을 머리글로 사용, 머리글을 첫 행으로 사용 두 가지 옵션이 나오는데요, 둘은 서로 정반대의 명령입니다.

첫 행을 머리글로 사용을 누르면 첫 행이 머리글로 올라와 새로운 열 이름이 지정되고, 보기의 열 분포로 가면 아까와는 다르게 열의 속성을 보여줍니다.

이제 드디어 쿼리 에디터에서 데이터 전처리 작업을 시작할 준비가 완벽하게 되었습니다.

가져온 데이터를 본격적으로 파워비아에서 사용할 수 있는 형태로 변환하기 위한 여러 가지 명령에 대해서도 알아보겠습니다.

엑셀에서 데이터 분석을 지원하기 위해 등장한 파워쿼리 에디터는 특별한 코딩 지식이 없는 분도 쉽게 데이터 전처리 진행이 가능하여지도록 했습니다.

엑셀과 비슷하게 구성되었기에 거부감 없이 접근할 수 있지만, 역으로 엑셀과는 너무나도 다른 작업환경에 크게 당황하게 되는데요.

사용이 그리 어렵지 않으니 하나하나 차근차근 명령 도구를 익히면 금방 익숙해집니다.

맨 위에 있는 쿼리 에디터 리본으로 모든 명령을 수행하게 됩니다.

이곳에서 실행한 모든 명령은 일명 M-Code라고 하는 파워쿼리를 구성하는 컴퓨터 언어로 저장될 뿐 아니라 오른편 적용된 단계 창을 통해 실행된 명령이 순서대로 기록이 되어 나중에 쿼리 수정에 유용하게 쓰입니다.

쿼리 에디터 리본은 홈, 변환, 열 추가, 보기, 도구, 도구의 5가지 메뉴를 가지고 있으나 실제로 데이터 전처리에서는 홈, 변환, 열 추가 정도가 데이터 변환에 사용됩니다.

홈, 변환, 열 추가 리본 내에도 세분화된 버튼이 있으며 이 버튼을 이용해 명령을 수행하거나 작업 창 내 열 이름을 선택 후 마우스 오른쪽 버튼을 선택하면 추가 명령이 나오는데, 이를 통해서도 입력할 수 있으므로 상황에 맞게 사용하면 됩니다.

엑셀의 경우 직접 셀을 선택해서 입력할 수 있지만, 파워쿼리의 경우 모든 입력은 에디터 리본이나 열 이름을 선택해서 나오는 명령을 통해서만 입력할 수 있습니다.

파워쿼리와 모양이 비슷해서 약간 혼동이 되는 것이 데이터 보기 창인데, 이곳에서도 작업이 가능하나 M-Code가 아니라 DAX를 이용해 데이터 변환을 시킨다는 것이 다릅니다.

파워쿼리 명령이나 DAX 수식은 서로 보완하기 위해 만들어진 것이라 데이터 전처리 과정에서도 서로 보완해 가며 최상의 결과를 얻게 하려는 하나의 방편이 아닌가 합니다.

M-Code와 DAX는 뭐가 더 뛰어난 입력 도구인가를 논하기보다는 적절한 곳에 필요한 도구를 사용하면 됩니다.

예를 들면, 가지고 온 데이터의 열을 없애는 것은 파워쿼리 에디터에서만 가능하며, 기존에 있는 데이터 테이블을 이용해 새로운 테이블을 만드는 것은 DAX가 더 효율적이라 할 수 있습니다.

이처럼 서로 다른 특성이 있으므로 M-Code와 DAX는 뭐가 더 뛰어난 입력 도구인가를 논하는 것은 큰 의미가 없는 것처럼 보이며 적절한 곳에 필요한 도구를 사용해서 가장 짧은 시간에 결과물을 만들어 내는 것이 중요하겠죠.

즉 파워쿼리 에디터는 데이터를 불러와서 DAX 사용에 가장 적합하게 데이터를 정리하고, 그리고 나머지 부분은 DAX로 채워 나가는 것이 가장 효율적인 방법이 아닐까 생각합니다.

쿼리 에디터에 있는 명령 도구도 사용하다 보면 자주 쓰이는 것이 있는데, 다음에는 자주 사용되는 버튼의 사용법에 대해 알아보도록 하겠습니다.