Cách sử dụng Power Query trong Excel?
Excel Power Query được sử dụng để tìm kiếm nguồn dữ liệu, tạo kết nối với nguồn dữ liệu, sau đó định hình dữ liệu theo yêu cầu phân tích của chúng tôi. Khi chúng tôi đã hoàn thành việc định hình dữ liệu theo nhu cầu của mình, chúng tôi cũng có thể chia sẻ những phát hiện của mình và tạo các báo cáo khác nhau bằng cách sử dụng nhiều truy vấn hơn.
Các bước
Về cơ bản, có 4 bước và thứ tự của 4 bước này trong Power Query như sau:
- Kết nối: Trước tiên, chúng tôi kết nối với dữ liệu, có thể ở đâu đó, trên đám mây, trong dịch vụ hoặc cục bộ.
- Chuyển đổi: Bước thứ hai sẽ là thay đổi hình dạng của dữ liệu theo yêu cầu của người dùng.
- Kết hợp: Trong bước này, chúng tôi thực hiện một số bước chuyển đổi và tổng hợp, đồng thời kết hợp dữ liệu từ cả hai nguồn để tạo báo cáo kết hợp.
- Quản lý: Điều này hợp nhất và nối các cột trong một truy vấn với các cột phù hợp trong các truy vấn khác trong sổ làm việc.
Có rất nhiều tính năng siêu mạnh mẽ của Excel Power Query.
Giả sử chúng ta có dữ liệu mua hàng trong 15 năm qua trong 180 tệp. Giờ đây, việc quản lý của một tổ chức sẽ yêu cầu tổng hợp các con số trước khi phân tích chúng. Ban quản lý có thể thực hiện bất kỳ phương pháp nào sau đây:
- Họ sẽ mở tất cả các tệp và sao chép-dán chúng vào một tệp.
- Mặt khác, họ có thể sử dụng một giải pháp khôn ngoan, đó là áp dụng các công thức, vì nó dễ bị sai sót.
Dù họ chọn phương pháp nào, nó cũng chứa rất nhiều thao tác thủ công và sau một vài tháng, sẽ có dữ liệu bán hàng mới trong thời gian thêm. Họ sẽ phải thực hiện lại bài tập tương tự.
Tuy nhiên, Power Query có thể giúp họ không làm công việc nhàm chán và lặp đi lặp lại này. Hãy để chúng tôi hiểu truy vấn power excel này với một ví dụ.
Thí dụ
Giả sử chúng ta có các tệp văn bản trong một thư mục có dữ liệu bán hàng và chúng ta muốn lấy dữ liệu đó trong tệp excel của mình.
Như chúng ta có thể thấy trong hình dưới đây, chúng ta có hai loại tệp trong thư mục, nhưng chúng ta muốn lấy dữ liệu của chỉ tệp văn bản trong tệp excel.
Để làm điều tương tự, các bước sẽ là:
Bước 1: Đầu tiên, chúng ta cần lấy dữ liệu trong Power Query để có thể thực hiện các thay đổi cần thiết trong dữ liệu để nhập dữ liệu đó vào tệp excel.
Để làm tương tự, chúng tôi sẽ chọn tùy chọn “Từ thư mục” từ menu “Từ tệp” sau khi nhấp vào lệnh “Lấy dữ liệu” từ nhóm “Lấy & chuyển đổi” trong tab “Dữ liệu” .
Bước 2: Chọn vị trí của thư mục bằng cách duyệt.
Nhấp vào 'OK'
Bước 3: Một hộp thoại sẽ mở ra chứa danh sách cho tất cả các tệp trong thư mục đã chọn với các tiêu đề cột là 'Nội dung,' 'Tên', 'Tiện ích mở rộng,' 'Ngày truy cập', 'Ngày sửa đổi', 'Ngày tạo', 'Thuộc tính' và 'Đường dẫn thư mục.'
Có 3 tùy chọn, tức là Kết hợp , Tải và Chuyển đổi dữ liệu .
- Kết hợp : Tùy chọn này được sử dụng để chuyển đến màn hình nơi chúng ta có thể chọn dữ liệu nào sẽ kết hợp. Bước chỉnh sửa bị bỏ qua đối với tùy chọn này và không cho chúng tôi kiểm soát việc kết hợp tệp nào. Chức năng kết hợp lấy mọi tệp trong thư mục để hợp nhất, điều này có thể dẫn đến lỗi.
- Tải: Tùy chọn này sẽ chỉ tải bảng như được hiển thị ở trên trong hình vào trang tính Excel thay vì dữ liệu thực tế trong tệp.
- Transform Data: Không giống như lệnh 'Combine' , nếu chúng ta sử dụng lệnh này, thì chúng ta có thể chọn các tệp để kết hợp, tức là chúng ta chỉ có thể kết hợp một loại tệp (cùng một phần mở rộng).
Như trong trường hợp của chúng tôi, chúng tôi chỉ muốn kết hợp các tệp văn bản (.txt); chúng ta sẽ chọn lệnh "Transform Data" .
Chúng ta có thể thấy “Các bước áp dụng” ở bên phải cửa sổ. Hiện tại, chỉ có một bước duy nhất được thực hiện là lấy chi tiết tệp từ thư mục.
Bước 4: Có một cột tên là 'Phần mở rộng' , nơi chúng ta có thể thấy rằng các giá trị trong cột được viết trong cả hai trường hợp, tức là chữ hoa và chữ thường.
Tuy nhiên, chúng ta cần chuyển đổi tất cả các giá trị thành chữ thường vì bộ lọc phân biệt giữa cả hai. Để làm tương tự, chúng ta cần chọn cột và sau đó chọn “Chữ thường” từ menu của lệnh “Định dạng” .
Bước 5: Chúng tôi sẽ lọc dữ liệu bằng cách sử dụng cột 'Tiện ích mở rộng' cho các tệp văn bản.
Bước 6: Bây giờ chúng ta cần kết hợp dữ liệu cho cả hai tệp văn bản bằng cách sử dụng cột đầu tiên 'Nội dung.' Chúng ta sẽ nhấp vào biểu tượng được đặt ở phía bên phải của tên cột.
Bước 7: Một hộp thoại có chú thích là 'Kết hợp tệp' sẽ mở ra nơi chúng ta cần chọn dấu phân cách là 'Tab' cho tệp văn bản (tệp có phần mở rộng '.txt' ') và có thể chọn cơ sở để phát hiện kiểu dữ liệu. Và nhấp vào 'OK.'
Sau khi nhấp vào 'OK', chúng tôi sẽ nhận được dữ liệu kết hợp của các tệp văn bản trong cửa sổ 'Power Query' .
Chúng tôi có thể thay đổi kiểu dữ liệu của các cột theo yêu cầu. Đối với cột 'Doanh thu' , chúng tôi sẽ thay đổi loại dữ liệu thành 'Đơn vị tiền tệ'.
Chúng ta có thể thấy các bước được áp dụng cho dữ liệu bằng cách sử dụng truy vấn nguồn ở phía bên phải của cửa sổ.
Sau khi thực hiện tất cả các thay đổi cần thiết trong dữ liệu, chúng ta có thể tải dữ liệu vào một trang tính excel bằng cách sử dụng lệnh 'Close & Load To' trong nhóm 'Close' trong tab 'Trang chủ' .
Chúng ta cần chọn xem chúng ta muốn tải dữ liệu dưới dạng Bảng hay Kết nối. Sau đó nhấp vào 'OK.'
Bây giờ chúng ta có thể xem dữ liệu dưới dạng bảng trong trang tính.
Và ngăn 'Truy vấn sổ làm việc ' ở phía bên phải, chúng ta có thể sử dụng để chỉnh sửa, sao chép, hợp nhất, nối các truy vấn và cho nhiều mục đích khác.
Excel Power Query rất hữu ích vì chúng ta có thể thấy rằng 601.612 hàng đã được tải trong vòng vài phút.
Những điều cần ghi nhớ
- Power Query không thay đổi dữ liệu nguồn ban đầu. Thay vì thay đổi dữ liệu nguồn ban đầu, nó ghi lại từng bước được người dùng thực hiện trong khi kết nối hoặc chuyển đổi dữ liệu và khi người dùng hoàn thành việc định hình dữ liệu, nó sẽ lấy tập dữ liệu đã tinh chỉnh và đưa nó vào sổ làm việc.
- Power Query phân biệt chữ hoa chữ thường.
- Trong khi hợp nhất các tệp trong thư mục được chỉ định, chúng tôi cần đảm bảo rằng sử dụng cột 'Tiện ích mở rộng' và chúng tôi phải loại trừ các tệp tạm thời (có phần mở rộng là '.tmp' và tên của các tệp này bắt đầu bằng dấu '~') như Power Query cũng có thể nhập các tệp này.
