Power Query – Công cụ tiền xử lý (ETL) dữ liệu hiệu quả
POWERQUERY LÀ GÌ?
Là công cụ hỗ trợ tiền xử lý dữ liệu hiệu quả Được tích hợp trong Excel và Power BI Gồm 3 nhóm chức năng chính là: LỢI ÍCH CỦA POWER QUERY
Tự động cật nhật khi có dữ liệu nguồn mới thay vì làm thủ công. Lưu trữ được các thao tác xử lý để có thể cập nhật, chỉnh sửa và nâng cấp Chuyển đổi được cả khối dữ liệu thay vì từng cột dữ liệu đơn lẻ (pivot, unpivot, transpose) Kết nối được nhiều bảng/nguồn dữ liệu (Merge, Append) Tách biệt được dữ liệu chưa xử lý và dữ liệu đã được xử lý để hệ thống phân tích vẫn hoạt động tốt khi dữ liệu nguồn mới chưa xử lý. Giao diện trực quan sinh động, lập trình mã nguồn ít.Trong hướng dẫn này cung cấp phần giải thích giới thiệu về Power Query. Bạn sẽ tìm hiểu lý do tại sao đây là công cụ Excel để làm việc với dữ liệu và cách nó có thể giúp tự động hóa các quy trình và tiết kiệm thời gian của bạn!
Máy dữ liệu Power Query
Chúng ta đang xem một chương trình truyền hình về cách mọi thứ được tạo ra, và họ đang giải thích cách một máy gửi tiền làm việc trong một nhà máy bánh ngọt.
Những điều cơ bản của máy là:
- Bạn thêm nguyên liệu vào máy.
- Thay đổi một số cài đặt.
- Và máy tạo ra bánh ngọt một cách kỳ diệu (bánh quy, bánh rán,…) đã sẵn sàng để nướng.
Sau khi thông số được cài đặt, quá trình có thể được lặp đi lặp lại nhiều lần để tạo ra những chiếc bánh ngọt hoàn hảo cho mỗi lần.
Power Query hoạt động theo cách rất giống nhau!
- Bạn thêm nguồn dữ liệu của mình (bảng Excel, tệp CSV, bảng cơ sở dữ liệu, trang web, v.v.)
- Nhấn các nút trong cửa sổ Power Query Editor để chuyển đổi dữ liệu của bạn.
- Xuất dữ liệu đó sang bảng tính hoặc mô hình dữ liệu của bạn (PowerPivot) đã sẵn sàng cho các pivot tables hoặc báo cáo.
Power Query giống như một cỗ máy bởi vì một khi bạn đã thiết lập query của mình, quy trình có thể được lặp lại với một lần bấm nút (làm mới) mỗi khi dữ liệu của bạn thay đổi.
Nếu bạn đã sử dụng macro để chuyển đổi dữ liệu của mình, bạn có thể nghĩ đây là một giải pháp thay thế dễ dàng hơn nhiều so với VBA KHÔNG yêu cầu mã hóa.
Nhiệm vụ dữ liệu chung được thực hiện dễ dàng
Bạn có làm việc với dữ liệu đã được xuất từ một hệ thống hồ sơ không? Đây có thể là một sổ cái chung, kế toán, ERP, CRM, Salesforce.com, hoặc bất kỳ hệ thống báo cáo nào có chứa dữ liệu.
Nếu vậy, bạn có thể dành nhiều thời gian để chuyển đổi hoặc định hình lại dữ liệu của mình để tạo báo cáo bổ sung, pivot tables hoặc biểu đồ.
Những biến đổi dữ liệu này có thể bao gồm các nhiệm vụ như:
- Xóa cột, hàng, khoảng trống
- Chuyển đổi các loại dữ liệu – văn bản, số, ngày
- Chia hoặc hợp nhất các cột
- Sắp xếp và lọc các cột
- Thêm các cột được tính toán
- Tổng hợp hoặc tóm tắt dữ liệu
- Tìm và thay thế văn bản
- Hủy dữ liệu để sử dụng cho pivot tables
Có bất kỳ nhiệm vụ trong số này nghe có vẻ quen thuộc? Nếu vậy, thì có lẽ chúng cũng nghe có vẻ nhàm chán, lặp đi lặp lại và tốn thời gian.
May mắn thay, Power Query có các nút tự động hóa tất cả các tác vụ này!
Tổng quan về Power Query Ribbon
Bắt đầu từ Excel 2016 cho Windows, Power Query đã được tích hợp hoàn toàn vào Excel. Nó hiện nằm trên tab Dữ liệu của Ribbon trong nhóm Get & Transform.
Trong Excel 2010 và 2013 cho Windows, Power Query là một bổ trợ miễn phí. Sau khi cài đặt, tab Power Query sẽ hiển thị trong Excel Ribbon.
Bạn sử dụng các nút trong tab Data hoặc Power Query để lấy dữ liệu nguồn của mình. Một lần nữa, dữ liệu của bạn có thể được lưu trữ trong các tệp Excel, tệp csv, Access, SQL server database, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, trang web, v.v.
Khi bạn đã chỉ định nơi dữ liệu của bạn đến từ đâu, sau đó bạn sử dụng cửa sổ Power Query Editor để thực hiện chuyển đổi dữ liệu.
Các nút trong Power Query Editor Window cho phép bạn chuyển đổi dữ liệu của mình.
Hãy suy nghĩ về một số nhiệm vụ bạn thực hiện liên tục khi bạn duyệt các nút trong hình trên. Mỗi lần bạn nhấn một nút, các hành động (các bước) của bạn được ghi lại và bạn có thể nhanh chóng áp dụng lại các bước khi bạn nhận được dữ liệu mới bằng cách làm mới query.
Sau khi hoàn thành các bước của mình, bạn có thể xuất dữ liệu vào Table trong Excel workbook của mình bằng cách nhấp vào nút Close & Load.
Bạn cũng có thể sửa đổi các query hiện có và làm mới các bảng đầu ra của bạn với các thay đổi hoặc dữ liệu cập nhật.
Ví dụ về Data Transformation
Dưới đây là một vài ví dụ về những gì Power Query có thể làm với dữ liệu của bạn.
Hủy dữ liệu cho Pivot Tables
Tính năng yêu thích về Power Query là khả năng Unpivot dữ liệu. Đây là một kỹ thuật được sử dụng để giúp dữ liệu của bạn sẵn sàng cho nguồn của pivot table. Điều này cũng được gọi là bình thường hóa dữ liệu của bạn để có được nó ở định dạng bảng.
Dữ liệu có thể bắt đầu trông giống như sau.
Và bạn muốn kết quả cuối cùng trông như thế này.
Power Query có thể thực hiện việc này bằng cách nhấp vào một vài nút và chuẩn bị dữ liệu của bạn để sử dụng trong pivot table.
Append (kết hợp) các bảng với Power Query
Tính năng Append của Power Query cho phép bạn kết hợp nhiều bảng (xếp chúng theo chiều dọc) để tạo một bảng lớn. Nó có thể làm điều này với nhiều bảng trong một tệp hoặc nó có thể lấy dữ liệu từ một loạt các tệp/nguồn khác nhau.
Giả sử bạn có một thư mục chứa các tệp CSV hoặc Excel có dữ liệu báo cáo cho mỗi tháng. Cho tất cả các tệp đó vào máy Power Query và nó sẽ cho ra một bảng đẹp mà sau đó bạn có thể sử dụng để tạo các bảng và biểu đồ.
Nếu dữ liệu trong các báo cáo đó cũng cần phải được chuyển đổi (xóa hàng, tách cột, unpivot, v.v.), thì Power Query có thể xử lý việc đó trong cùng một quy trình.
Sau khi được thiết lập, tất cả những gì bạn phải làm là nhấn nút refresh mỗi tháng khi một tệp mới được thêm vào thư mục và các hàng sẽ được thêm vào bảng đầu ra của bạn.
Merge Tables – Một thay thế của VLOOKUP
Power Query có khả năng hợp nhất hoặc nối các bảng. Điều này có thể được sử dụng thay thế cho các công thức VLOOKUP hoặc INDEX/MATCH.
Ví dụ, bạn có bảng dữ liệu hồ sơ bán hàng này và bạn đang sử dụng VLOOKUP để cung cấp thông tin về sản phẩm dựa trên tên của sản phẩm được bán. Thông tin nhóm sản phẩm của bạn được đặt trong một bảng khác trên một sheet hoặc workbook khác.
Sử dụng các hàm VLOOKUP là rất tốt, nhưng nó thường có nghĩa là thêm hàng ngàn công thức vào workbook của bạn. Làm tăng kích thước tập tin và thời gian tính toán.
Power Query giúp kết hợp hai bảng với nhau rất nhanh và dễ dàng chỉ bằng vài cú nhấp chuột. Về cơ bản, nó sử dụng các phép nối SQL, do đó bạn thậm chí có thể thực hiện các phép hợp nhất nâng cao hơn như các phép nối bên trong, bên ngoài, bên trái, bên phải, đầy đủ và chống tham gia.
Tạo chức năng tùy chỉnh
Power Query được thiết kế sao cho bạn KHÔNG phải biết cách viết mã để sử dụng nó. Nó rất dễ sử dụng vì bạn chỉ cần nhấp vào nút và áp dụng các bộ lọc như bạn thường làm trong Excel.
Tuy nhiên, Power Query có thể được lập trình để tạo các chức năng tùy chỉnh. Điều này mang đến cho bạn tiềm năng dường như không giới hạn để chuyển đổi dữ liệu của bạn theo bất kỳ cách nào có thể.
Nó dựa trên ngôn ngữ M và hầu hết các hàm rất giống với việc viết công thức trong Excel. Điều này cũng làm cho nó thân thiện hơn với người dùng và dễ dàng hơn để tìm hiểu mã.
Ngôn ngữ mới này và bộ chức năng có nghĩa là có rất nhiều thứ để học, nhưng đây lại là phần thú vị và đầy thách thức. Thêm vào đó, các nhà tuyển dụng tương lai chắc chắn sẽ tìm kiếm nhân viên có kỹ năng Power Query.
Power Query ghi lại các bước của bạn và tự động hóa các quy trình
Power Query không chỉ làm cho tất cả các tác vụ này trở nên dễ dàng hơn mà còn ghi lại các bước của bạn để bạn KHÔNG phải thực hiện lại nhiều lần. Nó sẽ giúp bạn tiết kiệm rất nhiều thời gian nếu bạn đang chuẩn bị cùng một dữ liệu mỗi ngày, tuần hoặc tháng.
Nó cũng xử lý lỗi khá tốt. Nếu cấu trúc dữ liệu nguồn của bạn thay đổi, Power Query sẽ cho bạn biết bước nào bị hỏng và cho phép bạn sửa nó. Điều này làm cho việc bảo trì dễ dàng và bạn không phải làm lại quy trình của mình khi có gì đó thay đổi.
Bạn có thể sử dụng Power Query để chuẩn bị sẵn sàng dữ liệu của mình trong các pivot table, biểu đồ và dashboard reports. Đây là một bước quan trọng trong quá trình tóm tắt và phân tích dữ liệu.
Power Query Machine & Power BI
Power Query chỉ là một phần trong bộ sản phẩm Power BI (Business Intelligence) của Microsoft.
Nếu chúng ta quay trở lại sự tương tự của cookie nướng trong nhà máy, bạn có thể nghĩ Power Query là bước đầu tiên trong dây chuyền lắp ráp. Khi cookie được hình thành, sau đó chúng ta cần phải nướng chúng (Pivot Tables, PowerPivot) và sau đó đóng gói chúng để trình bày (Power View, Power Map, Charts, Dashboards, v.v.)
Bạn có thể nghĩ về Excel như là công trình nhà máy chứa tất cả các công cụ này. Tất cả những tiến bộ trong công nghệ này sẽ giúp chúng ta hiểu được dữ liệu của mình theo những cách mới, tiết kiệm thời gian và gây ấn tượng với sếp.
Bạn có thể đánh giá trình độ phân tích dữ liệu tại đây để có thêm keyword cho lĩnh vực này.
Bạn cần tìm hiểu về khóa học Phân tích dữ liệu thì có thể đăng ký tại đây.
