Giới Power Query trong Excel

Có bốn giai đoạn để sử dụng Power Query.

1. Kết nối    Thực hiện kết nối với dữ liệu trên đám mây, trên một dịch vụ hoặc trên thiết bị

2. Biến    Định hình dữ liệu để đáp ứng nhu cầu của bạn, trong khi nguồn ban đầu vẫn không thay đổi

3. Kết hợp    Tích hợp dữ liệu từ nhiều nguồn để có được dạng xem duy nhất vào dữ liệu

4. Tải   Hoàn thành truy vấn của bạn và tải nó vào trang tính hoặc Mô hình Dữ liệu và định kỳ làm mới nó.

Các phần sau đây khám phá từng giai đoạn chi tiết hơn. 

Bạn có thể sử Power Query nhập vào một nguồn dữ liệu duy nhất, chẳng hạn như sổ làm việc Excel hoặc vào nhiều cơ sở dữ liệu, nguồn cấp dữ liệu hoặc dịch vụ nằm rải rác trên đám mây. Nguồn dữ liệu bao gồm dữ liệu từ Web, tệp, cơ sở dữ liệu, Bảng Azure hoặc thậm  chí là bảng Excel trong sổ làm việc hiện tại. Với Power Query, sau đó bạn có thể tập hợp tất cả các nguồn dữ liệu đó bằng cách sử dụng các phương pháp chuyển đổi và kết hợp độc đáo của riêng mình để phát hiện thông tin chuyên sâu mà bạn sẽ không nhìn thấy nếu không. 

Sau khi nhập, bạn có thể làm mới dữ liệu để bổ sung, thay đổi và xóa khỏi nguồn dữ liệu bên ngoài. Để biết thêm thông tin, hãy xem Làm mới kết nối dữ liệu ngoài trong Excel.  

Chuyển đổi dữ liệu có nghĩa là sửa đổi nó theo một cách nào đó để đáp ứng các yêu cầu phân tích dữ liệu của bạn. Ví dụ: bạn có thể loại bỏ cột, thay đổi kiểu dữ liệu hoặc lọc hàng. Mỗi thao tác trong số này là một chuyển đổi dữ liệu. Quy trình áp dụng chuyển đổi (và kết hợp) này cho một hoặc nhiều tập dữ liệu còn được gọi là định hình dữ liệu.

Hãy suy nghĩ về nó theo cách này. Bình hoa bắt đầu như một dốc đất sét mà một hình thành một cái gì đó thiết thực và đẹp mắt. Dữ liệu giống nhau. Cần định hình thành một bảng phù hợp với nhu cầu của bạn và cho phép các báo cáo và bảng điều khiển hấp dẫn.

Power Query sử dụng một cửa sổ chuyên dụng gọi là cửa sổ Trình soạn thảo Power Query tạo điều kiện và hiển thị chuyển đổi dữ liệu. Bạn có thể mở Trình soạn thảo Power Query bằng cách chọn Khởi chạy Trình soạn thảo truy vấn từ lệnh Lấy Dữ liệu trong nhóm Lấy Dữ liệu chuyển đổi & nhưng lệnh này cũng sẽ mở ra khi bạn kết nối với nguồn dữ liệu, tạo truy vấn mới hoặc tải truy vấn.

Công Trình soạn thảo Power Query theo dõi mọi thứ bạn thực hiện với dữ liệu bằng cách ghi và ghi nhãn cho mỗi thao tác chuyển đổi hoặc bước mà bạn áp dụng cho dữ liệu. Cho dù thao tác chuyển đổi là kết nối dữ liệu, loại bỏ cột, phối hay thay đổi kiểu dữ liệu, bạn đều có thể xem và sửa đổi từng thao tác chuyển đổi trong mục CÁC BƯỚC ĐÃ ÁP DỤNG của ngăn Thiết đặt Truy vấn .

Có rất nhiều chuyển đổi bạn có thể thực hiện từ giao diện người dùng. Mỗi chuyển đổi được ghi lại như một bước trong nền. Bạn thậm chí có thể sửa đổi và viết các bước của riêng mình bằng Power Query M Language trong Trình chỉnh sửa nâng cao.

Tất cả các chuyển đổi bạn áp dụng cho các kết nối dữ liệu của bạn chung tạo thành một truy vấn, đó là một bản trình bày mới của nguồn dữ liệu gốc (và không thay đổi). Khi bạn làm mới một truy vấn, mỗi bước sẽ chạy tự động. Truy vấn thay thế nhu cầu kết nối và định hình dữ liệu thủ công trong Excel.

Bạn có thể kết hợp nhiều truy vấn trong sổ làm việc Excel của mình bằng cách chắp thêm hoặc phối các truy vấn đó. Các thao tác Chắp thêm và Phối được thực hiện trên bất kỳ truy vấn nào có hình dạng bảng và độc lập với các nguồn dữ liệu bắt nguồn từ dữ liệu đó.

Nối   Phép toán chắp thêm sẽ tạo ra một truy vấn mới có chứa tất cả các hàng từ truy vấn đầu tiên theo sau là tất cả các hàng từ truy vấn thứ hai. Bạn có thể thực hiện hai loại thao tác chắp thêm:

• Chắp thêm Trung gian    Tạo truy vấn mới cho từng thao tác chắp thêm.

• Chắp thêm nội tuyến    Chắp thêm dữ liệu vào truy vấn hiện có của bạn cho đến khi bạn đến được kết quả cuối cùng.

Merge    Phép toán phối sẽ tạo ra một truy vấn mới từ hai truy vấn hiện có. Truy vấn này chứa tất cả các cột từ bảng chính, với một cột được dùng làm nối kết dẫn hướng đến bảng liên quan. Bảng có liên quan chứa tất cả các hàng khớp với mỗi hàng từ một giá trị cột chung trong bảng chính. Ngoài ra, bạn có thể bung rộng hoặc thêm các cột từ một bảng có liên quan vào bảng chính.

Có hai cách chính để tải truy vấn vào sổ làm việc của bạn:

• Từ vị Trình soạn thảo Power Query, bạn có thể sử dụng các lệnh Đóng và Tải trong nhóm Đóng trên tab Trang đầu.

• Từ ngăn Truy vấn Sổ làm việc Excel (Chọn Truy & Connections), bạn có thể bấm chuột phải vào một truy vấn và chọn Tải Đến.

Bạn cũng có thể tinh chỉnh tùy chọn tải của mình bằng cách sử dụng hộp thoại Tùy chọn Truy vấn (Chọn Tệp> Tùy chọn và thiết đặt > Tùy chọn Truy vấn) để chọn cách bạn muốn xem dữ liệu của mình và vị trí bạn muốn tải dữ liệu, trong trang tính hoặc Mô hình Dữ liệu (là nguồn dữ liệu có liên quan gồm nhiều bảng nằm trong sổ làm việc). 

Trong hơn 10 năm qua, Power Query đã được hỗ trợ trên Excel for Windows. Bây giờ, Excel đang mở rộng Power Query hỗ trợ trên Excel for Mac và thêm hỗ trợ cho Excel for the Web. Điều này có nghĩa là chúng tôi đang cung Power Query dụng trên ba nền tảng chính và thể hiện sự phổ biến và chức năng của Power Query trong số các khách hàng Excel. Xem các thông báo trong tương lai về lộ trình Microsoft 365và Có gì mới trong Excel cho Microsoft 365.

Việc tích hợp Lấy dữ liệu & đổi (hiện được gọi là Power Query), vào Excel đã trải qua một số thay đổi trong những năm qua. 

Excel 2010 và 2013 cho Windows

Trong Excel 2010 for Windows, chúng tôi đã giới thiệu Power Query lần đầu và nó có sẵn dưới dạng phần bổ trợ miễn phí có thể được tải xuống từ đây: Tải xuống phần bổ trợ Power Query này. Sau khi bật, Power Query năng đã sẵn dùng từ tab Power Query trên dải băng.

Microsoft 365

Chúng tôi đã Power Query đây là trải nghiệm chính trong Excel trong việc nhập và làm sạch dữ liệu. Bạn có thể truy nhập Power Query cụ và trình hướng dẫn nhập dữ liệu từ nhóm Lấy & Chuyển đổi Dữ liệu trên tab Dữ liệu của dải băng Excel.

Trải nghiệm này bao gồm chức năng nhập dữ liệu nâng cao, các lệnh được sắp xếp lại trên tab Dữ liệu, ngăn bên Kết nối & Truy vấn mới và khả năng liên tục định hình dữ liệu theo những cách mạnh mẽ bằng cách sắp xếp, thay đổi kiểu dữ liệu, tách cột, tổng hợp dữ liệu, v.v.

Trải nghiệm mới này cũng thay thế trình hướng dẫn nhập dữ liệu cũ, cũ bên dưới lệnh Dữ liệu trong nhóm Lấy Dữ liệu Ngoài . Tuy nhiên, bạn vẫn có thể truy nhập chúng từ hộp thoại Tùy chọn Excel (Chọn Tệp> Chọn > Dữ liệu> trình hướng dẫn nhập dữ liệu thừa tự).

Excel 2016 và 2019 cho Windows

Chúng tôi đã thêm trải nghiệm Tải & Đổi Dữ liệu giống nhau dựa trên công nghệ Power Query tương tự như của Microsoft 365.

Excel for Microsoft 365 for Mac

Trong năm 2019, chúng tôi đã bắt đầu hành trình hỗ Power Query trong Excel for Mac. Kể từ đó, chúng tôi đã thêm khả năng làm mới Power Query truy vấn mới từ các tệp TXT, CSV, XLSX, JSON và XML. Chúng tôi cũng đã thêm khả năng làm mới dữ liệu từ SQL server và từ bảng & phạm vi trong sổ làm việc hiện tại.

Vào tháng 10 năm 2019, chúng tôi đã thêm khả năng làm mới truy vấn Power Query hiện có và sử dụng VBA để tạo và chỉnh sửa truy vấn mới.

Vào tháng 1 năm 2021, chúng tôi đã bổ sung hỗ trợ cho việc làm mới truy vấn Power Query từ nguồn OData và SharePoint.

Để biết thêm thông tin, hãy xem sử Power Query dụng trong Excel for Mac.

Lưu ý    Không có hỗ trợ cho người Power Query trên Excel 2016 và Excel 2019 for Mac.

Data Catalog dùng

Với ứng dụng Data Catalog, bạn có thể xem các truy vấn được chia sẻ của mình, rồi chọn các truy vấn đó để tải, chỉnh sửa hoặc sử dụng trong sổ làm việc hiện tại. Tính năng này đã dần bị phản đối:

• Vào 01/08/2018, chúng tôi đã ngừng triển khai các khách hàng mới đến Data Catalog.

• Vào 03/12/2018, người dùng không thể chia sẻ truy vấn mới hoặc cập nhật trong Data Catalog.

• Vào 04/03/2019, công Data Catalog ngừng hoạt động. Sau ngày này, chúng tôi khuyên bạn nên tải xuống truy vấn được chia sẻ của mình để có thể tiếp tục sử dụng truy vấn bên ngoài Data Catalog, bằng cách sử dụng tùy chọn Mở từ ngăn tác vụ Truy vấn Data Catalog của Tôi.

Power Query dùng phần bổ trợ

Đầu mùa hè năm 2019, chúng tôi đã chính thức ngừng cung cấp phần bổ trợ Power Query bắt buộc cho Excel 2010 và 2013 for Windows. Như một sự lịch sự, bạn vẫn có thể sử dụng phần bổ trợ, nhưng điều này có thể thay đổi vào ngày sau đó.

Facebook nối dữ liệu đã ngừng hoạt động

Nhập và làm mới dữ liệu từ Facebook Excel đã ngừng hoạt động vào tháng Tư năm 2020. Bất kỳ Facebook kết nối nào được tạo trước ngày đó sẽ không còn hoạt động. Chúng tôi khuyên bạn nên sửa đổi hoặc loại bỏ bất kỳ truy vấn Power Query hiện có nào sử Facebook nối trong thời gian sớm nhất có thể để tránh các kết quả không mong muốn.

Excel for Windows các bản cập nhật quan trọng 

Từ tháng 6 năm 2023, Power Query trong Excel for Windows yêu cầu các cấu phần sau: 

• Power Query trong Excel for Windows sử dụng .NET framework nhưng yêu cầu phiên bản 4.7.2 trở lên. Để biết thêm thông tin, hãy xem Cập nhật .NET Framework.

• Power Query trong Excel for Windows yêu cầu WebView2 Runtime để tiếp tục hỗ trợ bộ nối Web dữ liệu (Lấy Dữ liệu từ Web).  Để biết thêm thông tin, hãy xem Tải xuống WebView2 Runtime.