Trong thời đại của các hồ dữ liệu và cơ sở dữ liệu quy mô petabyte, thật đáng ngạc nhiên là tôi vẫn thường xuyên nhận được dữ liệu ở dạng tệp CSV, văn bản và Excel. Mặc dù phân tích ngày nay tập trung vào những tiến bộ vượt bậc trong thuật toán học máy, công việc phân tích dữ liệu hàng ngày vẫn là một quy trình thủ công để tìm kiếm, biên dịch và giải quyết các loại dữ liệu khác nhau.
Đối với nhà phân tích tài chính, dữ liệu thường đến dưới dạng bảng tính Excel, nhưng thường xuyên, nó là kết xuất dữ liệu vào CSV hoặc truy vấn vào cơ sở dữ liệu SQL. Đôi khi, dữ liệu được sắp xếp theo một bố cục khó hiểu hoặc không có tất cả các thành phần cần thiết để phân tích. Thời gian dành cho việc kiểm tra dữ liệu này là thời gian quý báu bị lãng phí đối với nhà phân tích, tuy nhiên, đôi khi nhiệm vụ này được chấp nhận như một điều xấu cần thiết phải được chấp nhận.
Một giải pháp cho vấn đề phổ biến này thực sự khá dễ tiếp cận:Excel và Power BI có toàn bộ bộ công cụ chuyển đổi dữ liệu mà ít người dùng biết đến, được đặt tên là Get &Transform (trước đây gọi là Power Query). Việc sử dụng chức năng trích xuất, chuyển đổi và tải (ETL) được nhúng của nó cho phép các nhà phân tích tài chính liên kết liền mạch với các nguồn dữ liệu của họ và truy cập thông tin chi tiết nhanh hơn.
Khi chúng tôi chuẩn bị dữ liệu để tải vào Excel hoặc Power BI, chúng tôi thường phải thực hiện một số chuyển đổi đối với dữ liệu. Một số ví dụ về thao tác dữ liệu sẽ bao gồm:
Trong sơ đồ bên dưới, chúng ta thấy rằng Get &Transform thực hiện vai trò tẻ nhạt này là xử lý trước dữ liệu trước khi nó được tải.
Tại sao bạn nên học cách sử dụng Get &Transform? Chà, khi tôi xem xét cá nhân tôi đã sử dụng chức năng này để làm gì, nó đã cung cấp cho tôi một bộ công cụ dễ uốn cho:
Nói chung, khi tôi nhận được dữ liệu mới, tôi sẽ khám phá dữ liệu đó bằng cách sử dụng Get &Transform trước khi tải nó vào Power Pivot. Điều này cho phép tôi xem những phép biến đổi nào có thể cần thiết và nhanh chóng thực hiện một số trục và nhóm trên dữ liệu để tạo khuôn khổ cho phân tích. Trong nhiều trường hợp, ở giai đoạn này, tôi sẽ thấy rằng tôi cần thêm dữ liệu hoặc có vấn đề về dữ liệu. Bằng cách sử dụng nền tảng dựa trên Excel, tôi có thể nhanh chóng lặp lại với nguồn dữ liệu của mình để tìm những điểm bất thường về dữ liệu này.
Cuối cùng, quyết định ở lại Excel hay chuyển phân tích dữ liệu sang một nền tảng khác sẽ phụ thuộc vào đối tượng và khả năng lặp lại và phân phối của phân tích. Nếu khách hàng của tôi chỉ sử dụng Excel, thì hầu như tôi sẽ luôn sử dụng Get &Transform để tải dữ liệu, Power Pivot để thực hiện phân tích và Excel để tạo PivotTables và biểu đồ. Đối với khách hàng, điều này sẽ cảm thấy liền mạch vì tất cả đều được đặt trong Excel.
Tuy nhiên, nếu khách hàng của tôi:
Sau đó, tôi sẽ chỉ sử dụng Get &Transform để khám phá dữ liệu ban đầu và sau đó chuyển phần nặng nhọc sang R.
Trong các phiên bản Excel trước, Power Query là một phần bổ trợ có thể được cài đặt để trợ giúp với các hàm ETL. Tuy nhiên, trong Excel 2016 và Power BI, các công cụ này được tích hợp chặt chẽ hơn. Trong Excel 2016, chúng có thể được truy cập thông qua Dữ liệu rồi đến tab Nhận &chuyển đổi dữ liệu phần.
Trong Power BI, chức năng tồn tại trên Trang chủ trong tab Dữ liệu bên ngoài phần.
Trong bài viết này, các ví dụ của tôi diễn ra trong Power BI, nhưng giao diện gần như giống với Excel’s. Tôi sẽ chỉ ra những điểm khác biệt khi chúng phát sinh, do đó, hướng dẫn sẽ có ý nghĩa đối với cả hai loại người dùng.
Để hỗ trợ hướng dẫn này, tôi đã tạo một vài ví dụ về dữ liệu bán hàng cho một nhà bán lẻ hư cấu bán thiết bị và quần áo ngoài trời. Trong mỗi ví dụ này, dữ liệu sẽ được tạo ra theo những cách khác nhau để chứng minh các phương pháp kết xuất dữ liệu thực tế.
Như một ví dụ ban đầu, chúng ta sẽ thấy dữ liệu được trình bày dưới dạng một kết xuất dữ liệu lớn vào một tệp CSV. Yếu tố phức tạp là dữ liệu được trình bày với nhiều cột đại diện cho các cửa hàng khác nhau. Lý tưởng nhất là chúng tôi muốn nhập và chuyển đổi dữ liệu thành một bố cục dễ sử dụng hơn.
Dưới đây là ảnh chụp màn hình CSV thô trông như thế nào:
Tại sao chúng tôi muốn thay đổi điều này? Để tận dụng các khả năng quan hệ có thể có trong các ứng dụng này. Chúng ta sẽ thấy điều này diễn ra nhiều hơn trong cuộc thảo luận.
Hiện tại, giả sử rằng chúng ta cần xem dữ liệu là cấu trúc "hẹp hơn và cao hơn", thay vì "rộng hơn và ngắn hơn". Bước đầu tiên là tải CSV; sau đó, chúng tôi sẽ bắt đầu "giải nén" dữ liệu.
Như bạn có thể thấy, cấu trúc cuối cùng của dữ liệu hẹp hơn so với dữ liệu ban đầu và dài hơn rất nhiều. Một điểm khác là, khi chúng ta nhấp vào các hành động khác nhau, công cụ ở phía bên phải sẽ tạo ra một danh sách các bước được áp dụng được sử dụng để tạo truy vấn. Điều quan trọng là phải hiểu rằng điều này đang diễn ra trong nền, vì nó sẽ được xem lại sau.
Get &Transform trông và hoạt động tương tự giữa Power BI và Excel trong hầu hết các phần. Tuy nhiên, trong Excel, sau khi nhấp vào Đóng và tải , có một lời nhắc bổ sung. Trong hình bên dưới, chúng ta có thể chuyển đổi giữa việc chúng ta có muốn tải dữ liệu vào:
Ngoài ra, chúng tôi cũng được cung cấp tùy chọn có hay không Thêm dữ liệu này vào Mô hình Dữ liệu . Chọn hộp này sẽ tải dữ liệu vào bảng Power Pivot. Nếu chúng ta định phân tích dữ liệu trong Power Pivot, tôi khuyên bạn nên chọn Chỉ tạo kết nối và sau đó đảm bảo rằng Thêm dữ liệu này vào Mô hình dữ liệu tùy chọn được chọn. Nếu dữ liệu nằm trong giới hạn hàng trong Excel và chúng tôi muốn tiến hành phân tích trong Excel, thì chỉ cần chọn Bảng .
Trong clip tiếp theo, chúng ta sẽ thấy rằng lý do tại sao chúng tôi định dạng dữ liệu dài và mỏng là để chúng tôi có thể phân tích doanh số bán hàng không chỉ theo cửa hàng mà còn theo khu vực và tiểu bang. Để thực hiện nhiệm vụ này, chúng tôi sẽ nhập một bảng ánh xạ mỗi cửa hàng đến một khu vực và tiểu bang. Chúng ta sẽ thấy bên dưới rằng chúng ta có thể nhanh chóng tạo các báo cáo hiển thị doanh số bán hàng theo các nhóm khác nhau này.
Bạn có thể tưởng tượng loại khả năng này để chuyển đổi dữ liệu trong Excel, hoặc Power BI, có thể được áp dụng mạnh mẽ như thế nào cho bất kỳ trường hợp nào mà chúng tôi có các nhóm dữ liệu động, chẳng hạn như:
Trong khi bài viết này đề cập đến CSV và các tệp Excel khác, Get &Transform giải quyết nhiều loại dữ liệu. Khi một truy vấn được tạo, nó có thể được làm mới theo thời gian khi dữ liệu thay đổi.
Để chứng minh khả năng thao tác chuỗi của Get &Transform, tôi đã tạo một tập dữ liệu khác bắt chước một tệp văn bản hiển thị các giao dịch kế toán từ sổ cái chung của công ty (GL).
Chú ý làm thế nào số tài khoản và tên xuất hiện trong cùng một chuỗi? Trong Power BI, chúng ta có thể dễ dàng phân tích số tài khoản và tên thành các trường riêng biệt.
Trong video này, bạn có thể thấy rằng sau khi tôi tách cột, công cụ đoán rằng phía bên trái mới của trường Tài khoản phải là một số và nó tạo ra bước “Loại 1 đã thay đổi”. Vì cuối cùng chúng tôi muốn trường này là một chuỗi, chúng tôi có thể tiếp tục và xóa bước theo cách thủ công trong các bước đã áp dụng.
Tiếp theo, chúng tôi lấy cùng một dữ liệu và tạo một biểu đồ gồm các tài khoản có ánh xạ tới các danh mục tài khoản.
Tại sao chúng ta phải thực hiện tất cả các bước đó để lập bản đồ một vài số tài khoản? Một sổ cái thực có thể là hàng trăm, thậm chí hàng nghìn tài khoản. Truy vấn ánh xạ nhanh này, như chúng tôi đã trình bày, sẽ mở rộng đến mức đó mà không cần thực hiện thêm công việc nào.
Get &Transform hỗ trợ nhiều nguồn dữ liệu khác nhau. Mặc dù không phải là danh sách đầy đủ, nhưng dưới đây là một số ví dụ:
Tệp văn bản Excel Facebook Adobe Analytics Google Analytics Salesforce Azure Redshift Spark SQL Server SAP HANA Teradata Google BigQueryCá nhân tôi mới chỉ thử khoảng một nửa số kết nối trong danh sách trên. Mỗi đầu nối tôi đã sử dụng đều khá chắc chắn; Tôi đã chuyển từ dữ liệu thô đến thông tin chi tiết mà không phải làm nhiều việc nặng nề. Quan trọng không kém, nó đóng vai trò như một trình xác nhận giữa các nguồn dữ liệu khác nhau, đảm bảo rằng các đầu ra cuối có mức kiểm soát chất lượng được chuẩn hóa.
Trong nền, Get &Transform đang tạo mã mỗi khi chúng ta nhấp vào một nút trong công cụ hoặc thực hiện lựa chọn. Dưới đây là ví dụ về cách bạn truy cập mã cho truy vấn ánh xạ tài khoản mà chúng tôi đã tạo:
Mã sử dụng ngôn ngữ chức năng có tên M, ngôn ngữ này tự động tạo cho các trường hợp sử dụng cơ bản. Tuy nhiên, đối với những dữ liệu phức tạp hơn, chúng ta có thể chỉnh sửa và viết mã của riêng mình. Đối với hầu hết các trường hợp, tôi sẽ chỉ thực hiện các sửa đổi nhỏ đối với mã này. Trong các phép biến đổi phức tạp hơn, tôi có thể viết hầu hết mã từ đầu đến các bảng tạm thời hoặc để thực hiện các phép nối phức tạp hơn.
Excel có xu hướng đạt đến giới hạn của nó khi bạn cố gắng xuất hơn một triệu hàng. Trong trường hợp tôi đã chuyển đổi hàng triệu hàng với Get &Transform, cách duy nhất để gửi các hàng không được nhóm lại là thông qua các thủ thuật hoặc cách giải quyết tẻ nhạt. Tôi cũng nhận thấy rằng các truy vấn Get &Transform có thể không ổn định để triển khai cho nhiều người dùng, đặc biệt nếu bạn sử dụng nhiều nguồn dữ liệu và tham gia. Trong những trường hợp đó, tôi sẽ luôn sử dụng R để triển khai việc bao bọc dữ liệu có thể trùng lặp. Cuối cùng, Excel không được xây dựng để tạo mô hình dữ liệu nâng cao hơn. Bạn có thể thực hiện hồi quy tuyến tính khá nhanh chóng, nhưng ngoài ra, bạn sẽ cần phải sử dụng một nền tảng nghiêm ngặt hơn.
Sau khi nói tất cả những điều đó, tôi thấy rằng Excel là thứ mà hầu hết khách hàng của tôi cảm thấy thoải mái nhất. Excel vẫn là công cụ quan trọng nhất trong kho vũ khí của nhà phân tích tài chính. Bằng cách kết hợp chức năng Get &Transform, Excel và Power BI thậm chí còn trở nên mạnh mẽ hơn thông qua phạm vi nguồn dữ liệu mà chúng có thể chấp nhận.