Chuẩn hóa dữ liệu với Power Query trong phần mềm Microsoft Excel

Excel là phần mềm bảng tính đã quá quen thuộc với người dùng hiện nay. Tuy nhiên, để giải quyết những dữ liệu phức tạp giúp chúng trở nên chuyên nghiệp hơn thì người dùng nhất định không thể bỏ qua Power Query.

Power Query là gì?

Power Query được biết đến là một công cụ được sử dụng trong các phần mềm như Excel, Power BI… nhằm mục đích tạo ra một quy trình làm việc tự động cho doanh nghiệp hoặc tổ chức thông qua việc tái cấu trúc và xử lý dữ liệu.

power query

Nếu như người dùng sử dụng các phiên bản Excel 2013 trở về trước thì Power Query Excel sẽ là một trong những tiện ích mở rộng được trải nghiệm miễn phí bằng cách cài đặt hoặc không. Tuy nhiên, từ phiên bản 2016 trở lại đây công cụ này đã được tích hợp sẵn trong phần mềm Excel tựa như một tính năng mặc định để người dùng sử dụng. Để hiểu rõ hơn về công cụ xử lý dữ liệu chuyên nghiệp này, tiếp đến hãy cùng chúng tôi tìm hiểu về cách thức hoạt động mà người dùng có thể tham khảo bên dưới đây.

Cách thức hoạt động của Power Query Office 365

Power Query if được hoạt động dựa trên 04 giai đoạn chính là kết nối, chuyển đổi, kết hợp và tải. Để tìm hiểu chi tiết về từng giai đoạn,người dùng có thể tham khảo nội dung sau đây:

Giai đoạn 1 – Kết nối

Ở giai đoạn đầu tiên này, người dùng có thể sử dụng Power Query online để tiến hành nhập vào một nguồn dữ liệu duy nhất trên Excel hoặc bất cứ cơ sở dữ liệu nào có thể cung cấp ngay cả trên nền tảng đám mây. Các nguồn dữ liệu này có thể bao gồm tệp, cơ sở dữ liệu, Web, Bảng Azure.

Khi đó, người dùng sẽ tiến hành tập hợp toàn bộ dữ liệu này thông qua phương thức chuyển đổi và kết hợp như Power Query vs Power Pivot, Power Query vs Power BI… để tìm kiếm những thông tin chuyên sâu. Nếu dữ liệu đã được nhập thành công, người dùng có thể tiến hành xóa bỏ nguồn dữ liệu bên ngoài bất cứ lúc nào.

giai đoạn kết nối

Giai đoạn 2 – Chuyển đổi

Sau khi đã thực hiện xong giai đoạn kết nối, Power Query sẽ chuyển sang bước chuyển đổi để đáp ứng nhu cầu phân tích dữ liệu của người dùng như loại bỏ cột, thay đổi dữ liệu hoặc tiến hành lọc hàng. Khi đó, mỗi thao tác mà người dùng thực hiện trên công cụ này sẽ được xem là một bước chuyển đổi dữ liệu. 

giai đoạn chuyển đổi

Tại trình soạn thảo của Power Query, người dùng có thể khởi chạy trình soạn thảo truy vấn thông qua lệnh Lấy dữ liệu, lệnh này sẽ kết nối với nguồn dữ liệu để tải hoặc tạo truy vấn mới. Khi đó, người dùng có thể theo dõi mọi thao tác thực hiện bằng cách ghi nhãn, ghi chú để áp dụng cho chính dữ liệu của mình. 

Giai đoạn 3 – Kết hợp 

Chuyển sang giai đoạn tiếp theo của Power Query chính là bước kết hợp, điều này đồng nghĩa với việc người dùng có thể kết hợp nhiều truy vấn khác nhau trên chính cửa sổ làm việc Excel. Các thao tác có thể thực hiện được như thêm, phối bất kỳ dữ liệu nào dưới dạng bảng độc lập với dữ liệu ban đầu.

giai đoạn kết hợp

Giai đoạn 4 – Tải

Giai đoạn cuối cùng trong cách thức hoạt động của Power Query chính là tải truy vấn vào cửa sổ làm việc của người dùng. Khi đó, người dùng có thể tham khảo ngay 02 cách chính dưới đây:

  • Từ trình soạn thảo của Power Query, sử dụng các lệnh Đóng hoặc Tải trong nhóm ở tab trang đầu.
  • Từ ngăn truy vấn cửa sổ Excel, người dùng có thể bấm chuột phải vào một truy vấn và chọn Tải đến.

Ngoài ra, người dùng cũng có thể tùy chỉnh tải bằng cách sử dụng hộp thoại Tùy chọn tư vấn để xem dữ liệu và vị trí muốn tải trong trang tính hoặc mô hình dữ liệu.

Như vậy, công cụ Power Query sẽ hoạt động bằng cách trải qua một quy trình với đầy đủ 04 giai đoạn được chúng tôi gợi ý bên trên. Tiếp đến, người dùng có thể tham khảo thêm về vai trò thực tế của công cụ này trong chính doanh nghiệp thông qua nội dung tiếp theo.

Vai trò của Power Query trong phần mềm Excel

Sự xuất hiện của Power Query giúp cho người dùng bớt đi một gánh nặng lớn khi phải xử lý một khối lượng dữ liệu khổng lồ trên công cụ Excel. Chính vì vậy, chúng tôi đã chuẩn bị những vai trò chính của nền tảng này để người dùng có thể tham khảo như sau:

Tái cấu trúc bảng dữ liệu

Không phải lúc nào dữ liệu của người dùng cũng được thiết lập một cấu trúc đúng như mong muốn. Có rất nhiều trường hợp, bảng dữ liệu thiếu cột này, thừa cột kia, dữ liệu lộn xộn rời rạc khiến cho người dùng cảm thấy rất khó chịu và mất nhiều thời gian để thay đổi. Thay vì phải thao tác thủ công, người dùng có thể sử dụng Power Query để tự động hóa cấu trúc thông qua các hàm có điều kiện, sàng lọc dữ liệu và sử dụng Power Query Pivot để báo cáo, phân tích tự động.

cấu trúc bảng dữ liệu

Tập hợp dữ liệu

Vai trò tiếp theo của Power Query chính là tập hợp nhiều dữ liệu trên các file Sheet và Excel vào một bảng chung. Dựa trên bảng dữ liệu này, người dùng cũng có thể thực hiện Power Query function với cả left function và right function. Để thực hiện được, người dùng chỉ cần sử dụng một vài những câu lệnh đơn giản và chỉ áp dụng với một lần thực hiện duy nhất. Khi đó, với những lần thực hiện sau mọi dữ liệu sẽ được cập nhật theo bảng tổng hợp mà người dùng không cần tốn quá nhiều thời gian để thực hiện.

Với những vai trò được chúng tôi gợi ý trên đây, người dùng đã hiểu được ý nghĩa và tầm quan trọng khi sử dụng Power Query trong doanh nghiệp đặc biệt là kết hợp giữa PowerPivot vs Power Query hay Power Query BI, Excel.

Hướng dẫn tạo công thức Power Query trong Excel

Để hỗ trợ người dùng sử dụng Power Query hiệu quả trong phần mềm Excel bản quyền, chúng tôi sẽ hướng dẫn một vài các công thức cơ bản như sau:

Tạo công thức đơn giản

Chúng tôi sẽ lấy một ví dụ điển hình về cách tạo công thức đơn giản với Power Query ngay trên chính phần mềm Excel để chuyển đổi giá trị văn bản thành kiểu chữ thích hợp thông qua công thức Text.Proper().

Bước 1: Tại tab Ribbon của Power Query người dùng click chọn Từ nguồn khác sau đó chọn Truy vấn trống.

Bước 2: Một thanh công thức sẽ hiện ra, người dùng chỉ cần nhập “= Text.Proper(“text value”),” sau đó click chọn Enter.

tạo công thức đơn giản

Bước 3: Kết quả xuất hiện trên màn hình hiển thị, để xem kết quả này trên bảng tính Excel người dùng chỉ cần click chọn Đóng và Tải.

Bước 4: Hoàn tất.

Tạo công thức nâng cao

Bên cạnh việc tạo công thức đơn giản, người dùng có thể thực hiện tạo công thức nâng cao trên Power Query với nhiều bước thực hiện phức tạp hơn như sau:

Bước 1: Truy cập trình soạn thảo nâng cao trong phần mềm Excel

Chọn Power Query > Từ nguồn khác > Truy vấn trống. Tại mục Trình soạn thảo truy vấn người dùng chỉ cần click chọn Trình soạn thảo nâng cao.

mở trình soạn thảo nâng cao

Bước 2: Xác định nguồn ban đầu

Sử dụng lệnh let để gán Source = công thức Excel.CurrentWorkbook(). Tiếp đến, gán Source cho kết quả in.

let Source =

Excel.CurrentWorkbook(){[Name=”Orders”]}[Content]

in Source

Khi đó, người dùng có thể bấm Xong hoặc chọn Bấm & Tải.

kết quả trang tính

Bước 3: Chuyển đổi hàng đầu tiên hành tiêu đề

Để chuyển các giá trị thuộc cột tên sản phẩm thành kiểu chữ tích hợp, người dùng cần tiến hành tăng cấp hàng đầu tiên hành tiêu đề cột. 

Bổ sung #”First Row as Header” = công thức Table.PromoteHeaders() vào công thức truy vấn. 

Tiếp đến, tiến hành gán #”First Row as Header” cho kết quả in.

let

Source = Excel.CurrentWorkbook(){[Name=”Orders”]}[Content],

#”First Row as Header” = Table.PromoteHeaders(Source)

in

#”First Row as Header”

Kết quả nhận được:

kết quả tăng cấp

Bước 4: Thay đổi giá trị trong cột thành kiểu chữ thích hợp 

Bổ sung #”Capitalized Each Word” = công thức Table.TransformColumns() vào công thức truy vấn, tiếp đến là tham chiếu #”First Row as Header.

Tiến hành gán Gán #”Capitalized Each Word” cho kết quả in.

let

Source = Excel.CurrentWorkbook(){[Name=”Orders”]}[Content],

#”First Row as Header” = Table.PromoteHeaders(Source),

#”Capitalized Each Word” = Table.TransformColumns(#”First Row as Header”,{{“ProductName”, Text.Proper}})

in

 #”Capitalized Each Word”

Kết quả nhận được:

kết quả thay đổi giá trị

Bước 5: Hoàn tất.

Sau khi đã thực hiện xong, người dùng sẽ có một bảng dữ liệu được xử lý nhanh gọn và có thể ứng dụng Power Query in Power BI để tiếp tục tiến hành phân tích. 

Lời kết

Trên đây là những chia sẻ của MSO để hỗ trợ người dùng hiểu rõ hơn về công cụ Power Query và cách tạo công thức từ đơn giản đến nâng cao trên nền tảng này. Nếu người dùng cần nhận sự hỗ trợ của chúng tôi vui lòng liên hệ đến Hotline: 024.9999.7777.

0 0 Các bình chọn
Rating
Đăng ký
Thông báo của
guest

0 Bình luận
Phản hồi nội tuyến
Xem tất cả bình luận

Đăng ký nhận tin tức hằng ngày

Yêu cầu gọi lại

công cụ thiết kế sway microsoft

Sway Microsoft – Công cụ thiết kế slide chuyên nghiệp

Nếu người dùng đã quá nhàm chán với việc thiết kế slide trên công cụ PowerPoint thì có thể tham khảo ngay Sway Microsoft. Với ...

Microsoft Teams – Có thể bạn chưa biết

Người dùng hiện nay không còn cảm thấy xa lạ với phần mềm Microsoft Teams, nền tảng tổ chức cuộc họp trực tuyến tích hợp ...
05 mẹo sử dụng powerpoint online

05+ mẹo sử dụng Microsoft online PowerPoint nâng cao

Người dùng đã quá quen thuộc với phần mềm Microsoft online PowerPoint để tạo nên những bản slide chuyên nghiệp. Để sử dụng hiệu quả ...
dung lượng onedrive office 365

Tham khảo dung lượng OneDrive Office 365 và các gói đăng ký

Để bảo vệ dữ liệu, nhiều doanh nghiệp hiện nay đã tìm đến giải pháp lưu trữ trên nền tảng đám mây cực kỳ hiệu ...
Scroll to Top