DAX와 파워비아이

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

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

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

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

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

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

그룹화는 무엇인가?

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

그룹화 사용 방법

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

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

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

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

그룹화의 예

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

그룹화의 단점

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

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

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

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

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

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

지금까지 반복적으로 추가가 필요한 데이터 관리를 어떻게 하셨나요?

만약 데이터를 하나하나 추가한 뒤 다시 파워비아이를 수정하는 작업을 반복했다면, 폴더로 데이터 가져오기를 하면 아주 간단히 추가되는 데이터가 있어도 간단하게 폴더에 추가시킨 뒤 파워비아이에서 새로고침만 해주면 바로 모든 데이터가 보고서에 적용됩니다.

이는 Binary라는 좀 독특한 데이터 저장 방식이 있어서 가능한데요.

Binary에 저장된 모든 엑셀 데이터를 Excel.Workbook이라는 수식으로 불러와 새로운 열을 만들면 아무리 많은 데이터가 있더라도 한 테이블 안에 다 불러올 수 있습니다.

여기에 열 분할을 이용해 파일 이름을 새로운 연도 열로 만들어 적용시키면 데이터가 추가될때마다 기존 폴더에 파일만 넣어주면 몇 초 만에 새로운 데이터가 적용된 보고서를 얻을 수 있습니다.

파워쿼리 에디터를 통해 아주 간단히 만들 수 있는데, 한번 보시고 업무에 적용해 보세요.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

요즘 마이크로 소프트의 행보가 심상치 않습니다.

Chat GPT를 검색사이트 Bing에서 사용할 수 있도록 했는데요

구글 크롬에 밀려버린 존재감이 없었던 인터넷 검색 시장에  활기를 불어넣고 있으며  구글이게 빼앗긴 예전의 영화를 다시 찾아오려는 공격적인 행보를 보이는데 빅 테크 기업간의 경쟁이 우리에게 더 좋은 서비스로 돌아오니 나쁘지는 않은 것 같습니다.

게다가 Bing 검색 내에 제공하는 Chat GPT는 무료로 제공한다고 하니 이젠 따로 Chat GPT 사이트에 접속할 필요가 없다는 게 좋은 점이겠죠

간단하게 DAX 수식을 테스트해 보았습니다.

사이트를 열고 Bing Chat으로 접속한 모습입니다.

채팅하듯 아래의 입력창에 필요한 것을 입력합니다.

저는 프로젝트를 진행 할 때 직원들이 며칠 동안 일했는지를 알아보려, 주말을 제외한 일수를 알아보려 합니다.

그래서 먼저 영어로 아래와 같이 입력했습니다.

Chat GPT보다는 느렸지만 그래도 질문을 이해하고 알맞은 DAX 수식을 만들어 주었습니다.

그리고 놀라운 것은, 수식을 알려주면 공휴일을 뺀 수식을 알려달라고 말하려 했는데, 어찌 알았는지 묻지도 않았는데 공휴일을 제외한 일수를 구하는 DAX 수식도 함께 알려주었습니다.

내친김에, 다시 한글로 물어보았습니다.

보시는 것과 같이 영어로 입력했을 때와 똑같은 결과물을 보여줍니다.

영어나 한글, 둘 중 편한 것을 사용하면 될 것 같습니다.

수식을 받았으니 확인을 해 보아야겠죠?

테이블 뷰에서 열삽입을 하고 두 수식을 모두 넣었더니 만족할만한 결과치를 보여줍니다.

오늘은 간단한 수식만 시험해 봤는데, 복잡한 수식에 관한 것도 다음에 말씀드리겠습니다.

DAX 수식에 대한 부담이 줄어드니 파워비아이가 한층 더 쉽게 느껴지네요.

Chat GPT… 이거 정말 장난 아닙니다.!!!

파워비아이에 ChatGPT가 이용되면 많은 도움을 받을 수 있습니다.

파이썬이나 R 등의 컴퓨터 언어의 코딩을 도움받아 작업의 효율을 높일 수도 있지만, 가장 좋은 것은 DAX 수식을 자동으로 만들어 준다는 것이라 생각되는데요.

물론 잘 ChatGPT가 잘 알아들을 수 있도록 입력해야겠지만, 그래도 DAX 수식을 만들기 위해 여러 인터넷 사이트를 돌아다니는 수고를 줄일 수 있기에 엄청나게 시간을 절약할 수 있다는데  큰 의미를 부여하고자 합니다.

프로젝트를 진행하며 계속 테스트 중이기 때문에 어떤 질문 형태가 최적화된 것인지 곧 알게 될 것 같습니다. 

그동안 DAX라는 큰 벽에 가로막혀 파워비아이를 멀리하셨던 분들도 이 기회에 다시 한번 도전해 보시면 어떨까 합니다.

아래의 영상을 보시고 ChatGPT를 파워비아이게 사용해 보시기를 바랍니다.

ChatGPT라고 하는 인공지능을 기반으로 한 시스템이 연일 뉴스의 메인을 장식하고 있습니다.

다들 너나 할 것 없이 ChatGPT를 테스트하고 그 결과물에 많은 사람이 놀라움을 금치 못하고 있는데요.

역사나 사실관계에 대해서는 아직 잘못된 답을 보여주긴 하지만, 컴퓨터 언어의 코딩에서만큼은 비전공자의 실력은 충분히 뛰어넘고도 남을 레벨이라 인정해야 할 것 같습니다.

이는 파워비아이에서도 아주 유용하게 쓰일 것 같습니다.

아래와 같은 컴퓨터 언어를 능수능란하게 사용할 수 있다면 파워비아이를 좀 더 효율적으로 사용할 수 있겠죠

• Python
• R
• M
• DAX

Python은 이젠 더 이상 설명이 필요 없는 언어가 되었죠.파워비아이 에서는 Pandas라는 데이터분석 라이브러리를 이용해 데이터 전처리를 선행하면 시간과 공간을 절약할 수 있었습니다.

아직 사용해 보지는 않았지만, R 역시 데이터 분석에 최적화된 언어로 알고 있으며 파이썬 못지않게 쉬운 구성으로 되어있다고 알고 있습니다.

M은 파워쿼리를 구성하는 언어로 파워쿼리 에디터를 주로 사용하여 코딩을 공부해야 한다는 필요성을 크게 느끼지는 않지만 그래도 한 번씩 파워쿼리를 수정하는 작업을 할 때 코딩의 필요성을 느끼는 언어입니다.

마지막에 언급한 DAX는 엄밀히 말하자면 언어라기보다는 전처리된 데이터를 다시 한번 가공시키기 위한 수식이라 할 수 있겠습니다.

전공자가 아닌 이상 위에 말씀드린 언어를 제대로 공부한다는 것이 과연 효율적일 것일까 하는 생각이 드는 것도 사실이나, 그래도 모르는 것보다는 아는 것이 낫지 않을까 하는 마음으로 발을 들여놓았으나 아직 아주 기본적인 것만 할 수 있는 단계를 벗어나지 못하고 있는데, ChatGPT의 등장으로 이러한 고민이 한순간에 해결되었네요.

 내가 필요한 것을 아주 상세히 제대로만 입력하면, 원하는 결과치를 바로 만들어주고, 거기다 자세한 설명까지 알려주니 실로 엄청난 기술의 발전이 아닐까 합니다.

그리고 ChatGPT가 제시한 코드에 오류가 있더라도 이를 다시 입력하면 오류까지 바로잡아주니, 마치 선생님을 앞에 두고 작업을 하는듯한 생각마저 들었습니다.

그동안 아주 얕은 지식으로 여기저기 사이트를 돌아다니며 다른 분들이 이미 만들어 놓은 코딩을 복사해서 몇 날을 제 상황에 맞도록 수정해 가며 결국 원하는 바를 이루었을 때 느끼는 희열이 대단했는데, 이제는 나의 질문을 ChatGPT가 제대로 이해해서 제대로 된 코딩을 받았다면 그 질문의 내용에 대한 희열을 느껴야 할 것 같습니다.

이러한 기술의 발전이 하나의 고민을 해결해 주었으니, 이젠 그만큼의 시간을 다른 곳에 투자할 여력이 생긴 것 같아 좋긴 합니다.