데이터 레이크와 페타바이트 규모의 데이터베이스 시대에 CSV, 텍스트 및 Excel 파일 형식으로 데이터를 수신하는 빈도가 얼마나 많은지 충격적입니다. 현대의 분석은 기계 학습 알고리즘의 최첨단 발전에 초점을 맞추고 있지만, 데이터 분석의 일상적인 고된 작업은 여전히 서로 다른 데이터 유형을 찾고, 컴파일하고, 논쟁하는 수동 프로세스입니다.
재무 분석가에게 데이터는 종종 Excel 스프레드시트로 도착하지만 CSV로 데이터 덤프하거나 SQL 데이터베이스로 쿼리하는 경우가 많습니다. 데이터가 혼란스러운 레이아웃으로 배열되거나 분석에 필요한 모든 구성 요소가 없는 경우가 있습니다. 이 데이터를 스크러빙하는 데 소요되는 시간은 분석가에게 귀중한 시간 낭비이지만 때때로 이 작업은 용인되어야 하는 필요악으로 받아들여집니다.
이 일반적인 문제에 대한 솔루션은 실제로 매우 쉽게 접근할 수 있습니다. Excel 및 Power BI에는 Get &Transform(이전에는 Power Query라고 함)이라는 사용자가 거의 알지 못하는 전체 데이터 변환 도구 집합이 있습니다. 내장된 ETL(추출, 변환 및 로드) 기능을 사용하면 재무 분석가가 데이터 소스에 원활하게 연결하고 더 빠르게 통찰력을 얻을 수 있습니다.
Excel 또는 Power BI에 로드할 데이터를 티업할 때 일반적으로 데이터에 대한 몇 가지 변환을 수행해야 합니다. 데이터 조작의 몇 가지 예는 다음과 같습니다.
아래 다이어그램에서 Get &Transform이 데이터가 로드되기 전에 데이터를 사전 처리하는 이 지루한 역할을 수행하는 것을 볼 수 있습니다.
Get &Transform 사용법을 배우는 것이 왜 가치가 있습니까? 글쎄, 내가 개인적으로 이 기능을 사용한 이유를 살펴보면 다음을 위한 유연한 도구 세트를 제공했습니다.
일반적으로 새 데이터를 받으면 Power Pivot에 로드하기 전에 Get &Transform을 사용하여 탐색합니다. 이를 통해 어떤 변환이 필요할 수 있는지 확인하고 데이터에 대한 일부 피벗 및 그룹화를 신속하게 수행하여 분석을 위한 프레임워크를 공식화할 수 있습니다. 많은 경우 이 단계에서 더 많은 데이터가 필요하거나 데이터 문제가 있음을 알게 됩니다. Excel 기반 플랫폼을 사용하여 데이터 소스를 빠르게 반복하여 이러한 데이터 이상을 찾을 수 있습니다.
궁극적으로 Excel을 계속 사용할지 아니면 데이터 분석을 다른 플랫폼으로 옮길지 결정은 대상과 분석의 반복성 및 분포에 따라 달라집니다. 내 고객이 Excel만 사용하는 경우 거의 항상 Get &Transform을 사용하여 데이터를 로드하고 Power Pivot을 사용하여 분석을 수행하고 Excel을 사용하여 피벗 테이블 및 차트를 생성합니다. 고객에게는 이 모든 것이 Excel에 포함되어 있기 때문에 매끄럽게 느껴질 것입니다.
그러나 내 고객이 다음과 같은 경우:
그런 다음 초기 데이터 탐색에만 Get &Transform을 사용한 다음 무거운 작업을 R로 이동합니다.
이전 버전의 Excel에서 파워 쿼리는 ETL 기능을 지원하기 위해 설치할 수 있는 추가 기능이었습니다. 그러나 Excel 2016 및 Power BI에서는 이러한 도구가 더 긴밀하게 통합됩니다. Excel 2016에서는 데이터를 통해 액세스할 수 있습니다. 탭을 클릭한 다음 데이터 가져오기 및 변환 섹션.
Power BI에서 기능은 홈에 있습니다. 탭의 외부 데이터 섹션.
이 기사에서 내 예제는 Power BI에서 발생하지만 인터페이스는 Excel과 거의 동일합니다. 튜토리얼이 두 유형의 사용자 모두에게 이해가 될 수 있도록 차이점이 발생할 때 이를 지적하겠습니다.
이 튜토리얼을 돕기 위해 아웃도어 장비와 의류를 판매하는 가상의 소매업체에 대한 판매 데이터의 몇 가지 예를 만들었습니다. 이러한 각 예에서 데이터는 데이터 덤프의 현실적인 방법을 보여주기 위해 다양한 방식으로 생성됩니다.
초기 예에서는 CSV 파일에 대용량 데이터 덤프로 데이터가 표시되는 것을 볼 수 있습니다. 복잡한 요소는 데이터가 다양한 상점을 나타내는 여러 열로 표시된다는 것입니다. 데이터를 가져와 더 유용한 레이아웃으로 변환하는 것이 이상적입니다.
다음은 원시 CSV의 스크린샷입니다.
왜 이것을 변경하고 싶습니까? 이러한 응용 프로그램에서 가능한 관계 기능을 활용합니다. 우리는 이 논의가 더 진행되는 것을 보게 될 것입니다.
지금은 데이터를 "더 넓고 더 짧은" 구조가 아니라 "더 좁고 더 큰" 구조로 볼 필요가 있다고 가정해 보겠습니다. 첫 번째 단계는 CSV를 로드하는 것입니다. 그런 다음 데이터 "피벗 해제"를 시작합니다.
보시다시피 데이터의 최종 구조는 초기 데이터보다 좁고 훨씬 깁니다. 또 다른 요점은 다른 작업을 클릭할 때 오른쪽에 있는 도구가 쿼리를 작성하는 데 사용되는 적용된 단계 목록을 생성한다는 것입니다. 이는 나중에 다시 다루므로 백그라운드에서 진행되고 있음을 이해하는 것이 중요합니다.
Get &Transform은 대부분 Power BI와 Excel 간에 비슷하게 보이고 동작합니다. 그러나 Excel에서는 닫기 및 로드를 클릭한 후 , 하나의 추가 프롬프트가 있습니다. 아래 그림에서 데이터를 로드할지 여부를 전환할 수 있습니다.
또한 이 데이터를 데이터 모델에 추가할지 여부에 대한 옵션도 제공됩니다. . 이 상자를 선택하면 데이터가 파워 피벗 테이블로 로드됩니다. Power Pivot에서 데이터를 분석하려면 연결 만들기만을 선택하는 것이 좋습니다. 그런 다음 이 데이터를 데이터 모델에 추가 옵션이 선택됩니다. 데이터가 Excel 행 제한 내에 있고 Excel에서 분석을 수행하는 것을 선호하는 경우 표를 선택하기만 하면 됩니다. .
다음 영상에서는 데이터를 길고 가늘게 포맷한 이유는 매장별 뿐만 아니라 지역별, 주별 매출도 분석할 수 있도록 하기 위함입니다. 이 작업을 수행하기 위해 각 상점을 지역 및 주에 매핑하는 테이블을 가져옵니다. 아래에서 이러한 다양한 그룹별 판매를 표시하는 보고서를 빠르게 만들 수 있음을 알 수 있습니다.
Excel 또는 Power BI에서 이러한 유형의 데이터 변환 기능이 다음과 같이 데이터의 동적 그룹화가 있는 모든 경우에 어떻게 강력하게 적용될 수 있는지 상상할 수 있습니다.
이 기사에서는 CSV 및 기타 Excel 파일을 다루지만 Get &Transform에서는 광범위한 데이터 유형을 다룹니다. 쿼리가 생성되면 데이터가 변경됨에 따라 시간이 지남에 따라 새로 고칠 수 있습니다.
Get &Transform의 문자열 조작 능력을 보여주기 위해 회사의 총계정원장(GL)에서 회계 거래를 보여주는 텍스트 파일을 모방한 또 다른 데이터세트를 만들었습니다.
계좌 번호와 이름이 같은 문자열에 어떻게 나타나는지 알 수 있습니까? Power BI에서는 계정 번호와 이름을 별도의 필드로 손쉽게 구문 분석할 수 있습니다.
이 비디오에서 열을 분할한 후 도구가 계정 필드의 새 왼쪽이 숫자여야 한다고 추측하고 "변경된 유형1" 단계를 생성하는 것을 볼 수 있습니다. 궁극적으로 이 필드를 문자열로 원하기 때문에 적용된 단계에서 수동으로 단계를 삭제할 수 있습니다.
다음으로 동일한 데이터를 사용하여 계정 범주에 대한 매핑이 있는 계정과목표를 만듭니다.
몇 개의 계좌 번호를 매핑하기 위해 왜 이 모든 단계를 거쳐야 합니까? 실제 총계정원장은 수백 또는 수천 개의 계정이 될 수 있습니다. 이 빠른 매핑 쿼리는 추가 작업 없이 해당 수준으로 확장됩니다.
Get &Transform은 다양한 데이터 소스를 지원합니다. 전체 목록은 아니지만 다음은 몇 가지 예입니다.
텍스트 파일 Excel Facebook Adobe Analytics Google Analytics Salesforce Azure Redshift Spark SQL Server SAP HANA Teradata Google BigQuery개인적으로 위의 목록에 있는 연결 중 약 절반만 시도했습니다. 내가 사용한 각 커넥터는 상당히 견고했습니다. 나는 부담스러운 양의 작업 없이 원시 데이터에서 통찰력에 도달했습니다. 마찬가지로 중요한 것은 최종 출력이 표준화된 수준의 품질 관리를 갖도록 보장하는 서로 다른 데이터 소스 간의 유효성 검사기 역할을 한다는 것입니다.
백그라운드에서 Get &Transform은 도구의 버튼을 클릭하거나 선택할 때마다 코드를 생성합니다. 다음은 우리가 만든 계정 매핑 쿼리에 대한 코드에 액세스하는 방법의 예입니다.
이 코드는 기본 사용 사례에 대해 자동 생성되는 M이라는 기능 언어를 사용합니다. 그러나 더 복잡한 데이터 랭글링의 경우 자체 코드를 편집하고 작성할 수 있습니다. 대부분의 경우 이 코드를 약간만 수정하겠습니다. 더 복잡한 변환에서는 임시 테이블을 준비하거나 더 복잡한 조인을 수행하기 위해 대부분의 코드를 처음부터 작성할 수 있습니다.
Excel은 백만 개 이상의 행을 내보내려고 하면 한계에 도달하는 경향이 있습니다. Get &Transform을 사용하여 수백만 개의 행을 변환한 경우 그룹화되지 않은 행을 내보내는 유일한 방법은 지루한 해킹이나 해결 방법을 통하는 것입니다. 또한 특히 여러 데이터 원본과 조인을 사용하는 경우 Get &Transform 쿼리를 여러 사용자에게 배포하는 것이 불안정할 수 있다는 것도 발견했습니다. 이러한 경우에는 항상 R을 사용하여 복제 가능한 데이터 랭글링을 배포합니다. 마지막으로 Excel은 고급 데이터 모델링을 위해 만들어지지 않았습니다. 선형 회귀를 매우 빠르게 수행할 수 있지만 그 이상으로 더 엄격한 플랫폼을 사용해야 합니다.
이 모든 것을 말했지만 Excel은 대부분의 고객이 가장 편안하게 사용하는 것입니다. Excel은 여전히 재무 분석가의 무기고에서 가장 중요한 도구입니다. 가져오기 및 변환 기능을 통합함으로써 Excel과 Power BI는 수용할 수 있는 데이터 원본 범위를 통해 더욱 강력해집니다.