Làm thế nào để các truy vấn quyền lực làm việc trong excel?
|
Mục tiêu của hướng dẫn/hướng dẫn này là cung cấp cho bạn một điểm khởi đầu tuyệt vời để hiểu những gì Power Query có thể làm và cách sử dụng các chức năng cơ bản của nó. Hãy nghĩ về nó như một phiên bản rút gọn của “Power Query for Dummies” Show
Power Query là một công cụ hiện là một phần của Microsoft Excel và nó chủ yếu dựa vào việc người dùng nhấp vào các nút (không phải viết mã theo cách thủ công) để thực hiện các bước tự động hóa mong muốn của bạn. Nó là một sự thay thế tuyệt vời cho các giải pháp sao chép mà trước đây chỉ có thể thực hiện được với số lượng lớn mã hóa VBA (macro) Xem kích thước đầy đủ  Nhấp để phóng to Có nhiều khả năng nâng cao mà Power Query có thể thực hiện nhưng những khả năng đó có thể được nghiên cứu chính xác hơn bên ngoài hướng dẫn này. Sau khi dành vài phút để đọc bài đăng này, bạn sẽ được trang bị nhiều hơn là bắt đầu chạy và xây dựng giải pháp tự động đầu tiên của mình bằng cách sử dụng Power Query trong Excel Nếu bạn muốn theo dõi dữ liệu ví dụ đơn giản được sử dụng xuyên suốt hướng dẫn này, hãy nhấp vào nút bên dưới để tải xuống trực tiếp tệp ví dụ Excel ⭳ Tải xuống tệp ví dụ Hướng dẫn bằng video về Power QueryNếu bạn muốn tìm hiểu thêm qua video, đây là một số YouTuber Excel yêu thích của tôi cung cấp phần giới thiệu về Power Query Nội dung hướng dẫn
Power Query làm gì?Power Query là gì?Power Query là một giải pháp miễn phí được Microsoft phát triển cho Excel để giúp người dùng tự động hóa việc hợp nhất, dọn dẹp và chuyển đổi dữ liệu thông qua các bước tự động. Điểm tuyệt vời của Power Query là người mới bắt đầu có thể thực hiện tất cả những điều này mà không cần phải viết mã bất kỳ thứ gì, tuy nhiên, người dùng nâng cao vẫn có khả năng viết mã các phép biến đổi dữ liệu phức tạp hơn Power Query cho phép bạn tải nhiều tập dữ liệu vào trình chỉnh sửa của nó. Trong trình chỉnh sửa, có nhiều nút Ribbon khác nhau mà bạn có thể nhấp vào để loại bỏ trùng lặp, xóa hàng, định dạng ngày, v.v.. chỉ để đặt tên cho một số. Nhiều thao tác tự động hóa dữ liệu chỉ có thể thực hiện được thông qua mã hóa VBA giờ đây có thể dễ dàng thay thế thông qua công cụ Power Query Khi bạn đã tạo tất cả các hành động tự động của mình (còn gọi là Các bước), Power Query sẽ lưu trữ mọi thứ trong phần phụ trợ của tệp Excel và gửi kết quả cuối cùng của bạn vào một Bảng Excel mới trong bảng tính của bạn. Lần tới khi bạn cần cập nhật bảng tính của mình với dữ liệu mới, bạn chỉ cần nhấp vào nút làm mới và tất cả các bước của bạn sẽ tự động được áp dụng Sơ lược về lịch sử của Power QueryPower Query bắt đầu dưới dạng phần bổ trợ do Microsoft xuất bản cho Excel vào năm 2010. Các khả năng của phần bổ trợ được tích hợp trực tiếp vào Excel bắt đầu từ việc phát hành Excel 2016. Giao diện người dùng bổ trợ Power Query đã được thu gọn và hiện nằm trong tab Dữ liệu trong Nhận & Chuyển đổi Vào năm 2022, Microsoft đã tích hợp chức năng Power Query cho phiên bản Excel dành cho Mac. Microsoft vẫn đang làm việc để đưa khả năng này vào Excel Online Power Query hiện cũng là một công cụ quan trọng để chuẩn bị trước dữ liệu để tải vào bảng điều khiển Power BI Power Query có thể làm gì?Microsoft tóm tắt cách dữ liệu chảy qua Power Query bằng 4 giai đoạn
Power Query ở đâu trong Excel?Thật không may, Microsoft đã e ngại về sự khan hiếm của tên ban đầu của công cụ, “Power Query” khi họ nhúng nó trực tiếp vào Dải băng của Excel. Điều này khiến họ phải đổi tên thành một cái tên "thuần phục" hơn của Get & Transform Data (nhiều MVP của Excel đã rất cố gắng ngăn cản Microsoft làm điều này) Vì Power Query đã xuất hiện được vài năm nên phần lớn tài liệu được xuất bản cho dù đó là sách, video hay bài báo đều sử dụng tên ban đầu của công cụ. Làm cho vấn đề trở nên tồi tệ hơn, Microsoft đã không loại bỏ hoàn toàn tất cả các dấu vết của tên này vì chúng tôi vẫn sử dụng tên Trình soạn thảo Power Query thay vì “Trình soạn thảo Get & Transform” Không cần phải nói, khả năng khám phá của Power Query ở mức khủng khiếp nhất. Vậy làm thế nào để bạn truy cập Power Query? Nếu bạn nghĩ về 4 giai đoạn của Power Query, bạn bắt đầu bằng cách cung cấp cho nó một số dữ liệu. Các trình kết nối dữ liệu này được tìm thấy trong tab Dữ liệu của Excel. Khi ở trên tab Dữ liệu, bạn sẽ thấy nhãn nhóm nút Nhận & Chuyển đổi Dữ liệu ở phía ngoài cùng bên trái của Dải băng Nếu bạn muốn mở trực tiếp Power Query Editor, có một nút để khởi chạy nó bên dưới nút menu Lấy dữ liệu. Bản thân trình chỉnh sửa sẽ khởi chạy trong một cửa sổ ứng dụng riêng biệt và không may vô hiệu hóa bảng tính Excel của bạn khi trình chỉnh sửa đang mở Cách lấy dữ liệu vào Power QueryCó rất nhiều nguồn dữ liệu (còn gọi là kết nối) mà bạn có thể sử dụng để gửi dữ liệu thô vào Power Query để sau đó bắt đầu thao tác. Microsoft tiếp tục thêm vào danh sách dài các hộp thoại kết nối chuyên dụng. Hãy xem xét một số phổ biến nhất Ví dụ về những gì bạn có thể kết nối với
Điều tuyệt vời về Power Query và sự tích hợp của nó trong Excel là rất dễ thiết lập kết nối với nguồn dữ liệu của bạn. Vì bạn đang cố gắng hiểu cơ bản về cách thức hoạt động của Power Query, nên chúng ta sẽ ở trong Excel và làm việc thông qua việc thiết lập các kết nối tới Bảng bên trong bảng tính mà bạn đã sao chép bằng dữ liệu báo cáo từ một hệ thống khác Xem kích thước đầy đủ Ví dụ hướng dẫn với bảng ExcelTrong suốt hướng dẫn này, chúng tôi sẽ làm việc với hai bảng trên được cập nhật hàng tháng. Chúng tôi sẽ làm việc thông qua việc kết hợp hai bảng dữ liệu này thành một tập dữ liệu sạch để chúng tôi có thể dễ dàng xoay vòng hoặc tóm tắt dữ liệu Một điều quan trọng bạn nên giữ thói quen làm là đổi tên các đối tượng bảng của bạn thành một cái gì đó có ý nghĩa. Bạn có thể thực hiện điều này thông qua tab Thiết kế Bảng xuất hiện trong Dải băng sau khi bạn đã chọn một ô trong Đối tượng Bảng (hiển thị ở trên). Tên bảng sẽ đóng vai trò là lời nhắc về nội dung được lưu trữ bên trong bảng, do đó bạn không cần phải quay lại bảng tính của mình sau khi đã ở trong Power Query Editor Thiết lập kết nối với dữ liệu của bạnVì chúng tôi sẽ sử dụng Bảng Excel làm nguồn dữ liệu của mình, nên chúng tôi chỉ cần sử dụng nút Từ bảng/Phạm vi để tạo ngay một kết nối Power Query Xem kích thước đầy đủ Bạn có thể làm điều này bằng một trong hai
Sau khi bạn đã điều hướng đến nút kết nối thích hợp, bạn sẽ cần điền vào hộp thoại xuất hiện hoặc trong trường hợp này, kết nối sẽ tự động được tạo và Power Query Editor sẽ tự động mở Xem kích thước đầy đủ Bạn sẽ lưu ý rằng bản xem trước của dữ liệu được hiển thị và Power Query cũng đưa ra dự đoán có giáo dục về cách định dạng dữ liệu. Bạn cũng có thể thấy một Truy vấn đã được thiết lập với tên bảng của bạn trong ngăn bên trái Hãy tạm dừng ở đây và dành chút thời gian để xem lại các phần khác nhau của Power Query Editor Tổng quan về Power Query EditorPower Query Editor mở trong cửa sổ ứng dụng riêng của nó. Thật không may, trong khi nó đang mở, bạn không thể điều hướng trở lại bảng tính Excel của mình và khám phá/sửa đổi nó. Điều này giống như một lỗi, nhưng Microsoft đã thừa nhận đó là do thiết kế và cho đến nay đã từ chối cho phép chúng tôi làm việc trong cả Power Query và Excel cùng một lúc. Hy vọng, một ngày nào đó điều này sẽ thay đổi Trình chỉnh sửa sẽ cảm thấy quen thuộc vì nó sử dụng nhiều thành phần giao diện người dùng từ Microsoft Office. Về cơ bản có 4 vùng bạn sẽ nhận thấy trong trình chỉnh sửa
Hãy đi sâu vào từng lĩnh vực một chút nữa để bạn có phần giới thiệu về mục đích của từng lĩnh vực này Xem kích thước đầy đủ Nhấp để phóng to Băng rôn
Ngăn truy vấnMỗi bảng dữ liệu bạn kết nối với Power Query sẽ tạo một thứ gọi là Truy vấn. Các Truy vấn này sẽ nằm trong Ngăn Truy vấn nơi bạn có thể sửa đổi dữ liệu. Bạn có thể tạo các bản sao của Truy vấn, tạo Truy vấn của riêng mình từ đầu hoặc thậm chí hợp nhất hai hoặc nhiều truy vấn để tạo một Truy vấn hoàn toàn mới Xem trước bảngKhu vực này sẽ hiển thị cho bạn bản xem trước mẫu của đầu ra dữ liệu dựa trên Bước hiện được chọn của bạn trong hộp danh sách Các bước đã áp dụng. Ngoài ra còn có các tự động hóa mà bạn có thể yêu cầu Power Query ghi cho mình bằng cách chọn lọc một cột theo cách cụ thể hoặc nhấp chuột phải vào cột mục tiêu Chỉnh sửa mã tự động hóa
Phần Thuộc tính (trong Ngăn Cài đặt Truy vấn)Phần Thuộc tính cho phép bạn đổi tên Truy vấn của mình hoặc cung cấp mô tả để ghi lại những gì tự động hóa truy vấn của bạn thực hiện Bộ chọn bước (trong Ngăn Cài đặt Truy vấn)Mỗi hành động bạn thực hiện trong Power Query sẽ tạo ra một Bước. Mỗi Bước có thể được xem hoặc sửa đổi trong hộp danh sách Các bước đã áp dụng. Bạn có thể thay đổi thứ tự các bước của mình, xóa chúng hoặc đổi tên của chúng thành thứ gì đó có ý nghĩa hơn. Hầu hết các bước có hộp thoại thân thiện với người dùng có thể được sử dụng để quản lý hoặc bạn có thể sử dụng thanh công thức/Trình chỉnh sửa nâng cao để sửa đổi mã theo cách thủ công Nút trợ giúpNếu muốn xem các bài viết trợ giúp của Microsoft, bạn có thể nhấp vào biểu tượng dấu chấm hỏi màu xanh. Nút liên kết trực tiếp đến trang web hỗ trợ Power Query chính thức của Microsoft Hợp nhất hai bảng với nhauBây giờ bạn đã quen thuộc với Power Query Editor, hãy quay lại dữ liệu mẫu của chúng tôi và bắt đầu làm việc với Power Query Nếu bạn nhớ lại 4 giai đoạn của Power Query mà chúng ta đã thảo luận trước đó trong bài viết này (Kết nối, Kết hợp, Chuyển đổi và Tải/Xuất), thì chúng ta đang bắt đầu giai đoạn Kết nối Xin nhắc lại, bên dưới là 2 bảng chúng ta đang làm việc và muốn nạp vào Power Query Xem kích thước đầy đủ Hãy bắt đầu bằng cách kết nối bảng Báo cáo Tháng trước (có tên là TBL_PriorMonth) vào Power Query Chỉ cần chọn một ô trong bảng (ví dụ Ô C5) và nhấp vào nút Từ bảng/Phạm vi trong tab Dữ liệu. Các bước này được hiển thị trong ảnh chụp màn hình bên dưới Xem kích thước đầy đủ Sau khi nhấp vào nút Từ bảng/Phạm vi, Cửa sổ Power Query sẽ tự động mở và tạo truy vấn đầu tiên của bạn Bạn sẽ nhận thấy một số điều Power Query đã giúp bạn bắt đầu
Xem kích thước đầy đủ Chúng ta có thể bắt đầu thao tác với dữ liệu của mình ngay bây giờ, nhưng hãy quay lại và kết nối bảng thứ hai của chúng ta với Power Query. Cuối cùng, chúng tôi muốn hợp nhất cả hai bảng thành một truy vấn tổng hợp trước khi bắt đầu dọn dẹp dữ liệu Để lưu truy vấn của chúng tôi, hãy làm theo các bước sau
Sau đó, Power Query sẽ tự đóng và bạn sẽ được đưa trở lại Excel Xem kích thước đầy đủ Bây giờ, lặp lại các bước tương tự để kết nối bảng Báo cáo tháng hiện tại (TBL_Curr Month) với Power Query. Khi Power Query Editor mở lại, bạn sẽ thấy truy vấn thứ hai được tạo tương tự như truy vấn đầu tiên Xem kích thước đầy đủ Cuối cùng, hãy hợp nhất hai bảng này thành một bảng mới mà sau đó chúng ta có thể tiến hành cấu hình lại cho phù hợp với nhu cầu của mình Có hai tùy chọn để kết hợp truy vấn
Vì chúng tôi đang làm việc với hai lần lặp lại của cùng một báo cáo, nên chúng tôi sẽ sử dụng chức năng Nối để kết hợp các truy vấn của chúng tôi Để thực hiện việc này, hãy điều hướng đến nút Thêm truy vấn dưới dạng mới và chọn cả hai tên bảng, sau đó bấm OK Xem kích thước đầy đủ Sau khi bạn đã thực thi chức năng Append, bạn sẽ thấy một truy vấn bổ sung được tạo có tên là Append1. Bước duy nhất được tạo sẽ là Bước có tên Nguồn lưu trữ mã của hàm Kết hợp (cũng được hiển thị trong Thanh công thức) Xem kích thước đầy đủ Xin chúc mừng, bạn đã hoàn thành một số tự động hóa bằng Power Query. Hãy tìm hiểu sâu hơn và bắt đầu dọn dẹp bộ dữ liệu này để chúng tôi có thể báo cáo hiệu quả hơn Tự động làm sạch dữ liệu của bạnCó rất nhiều hành động thao tác hoặc làm sạch dữ liệu khác nhau mà bạn có thể thực hiện bên trong Power Query. Một số khả năng tự động hóa này bao gồm
Vì có quá nhiều chức năng cần trình bày trong phần này nên tôi sẽ thực hiện một số thao tác phổ biến hơn mà bạn có thể thực hiện. Điều này sẽ cung cấp cho bạn một nền tảng tốt và nếu bạn có nhu cầu làm sạch/thao tác dữ liệu bổ sung, chỉ cần Google nó cùng với cụm từ “Power Query”. Rất có thể sẽ có một giải pháp có thể dễ dàng tìm thấy. Hãy đi sâu vào nó Đặt kiểu dữ liệuMột vấn đề có thể xảy ra khi hợp nhất báo cáo là có các loại dữ liệu khác nhau trong cùng một cột. Điều này có thể dễ dàng làm rối các công thức hoặc biểu đồ dựa trên dữ liệu Power Query cho phép bạn chỉ định loại dữ liệu sẽ có trong một cột cụ thể. Power Query sẽ cố gắng đoán xem dữ liệu sẽ bắt đầu như thế nào hoặc nếu bạn đang hợp nhất từ các truy vấn khác, nó sẽ dựa vào các chỉ định từ các truy vấn nguồn Xem kích thước đầy đủ Thay đổi kiểu dữ liệu của cột rất dễ dàng. Chỉ cần nhấp vào biểu tượng nhỏ bên cạnh tên cột và chọn loại bạn muốn chỉ định Nếu có bất kỳ xung đột nào với những gì bạn đã chỉ định, một chỉ báo lỗi sẽ xuất hiện Trong ví dụ của chúng tôi, chúng tôi sẽ đảm bảo cột NGÀY có kiểu dữ liệu ngày và cột DOANH THU có kiểu dữ liệu tiền tệ Xem kích thước đầy đủ Bạn sẽ thấy rằng khi cột NGÀY được cập nhật, một lỗi đã được gắn cờ. Điều này là do có ngày 30 tháng 2 và đó không phải là ngày hợp lệ vì chỉ có 28 ngày vào tháng 2 năm 2022. Chúng tôi sẽ xử lý lỗi này ở phần sau Bạn cũng sẽ lưu ý rằng nếu bạn thực hiện liên tiếp các loại hành động giống nhau, thì Power Query có thể kết hợp chúng thành một bước duy nhất. Vì chúng tôi đã thay đổi hai kiểu dữ liệu cột đối lưng nhau nên Power Query đã mã hóa hai hành động đó thành một bước duy nhất. Bạn có thể thấy điều này được viết như thế nào trong Thanh công thức
Tìm thay thếTìm/Thay thế có thể là một hành động rất mạnh mẽ trong khi làm sạch dữ liệu của bạn. Power Query được gọi đơn giản là Giá trị thay thế này. Để thêm tự động tìm/thay thế vào các bước của bạn, hãy làm như sau
Xem kích thước đầy đủ Trong ví dụ của chúng tôi, chúng tôi sẽ thiết lập các Bước thay thế sau trong cột SẢN PHẨM
Lưu ý, không giống như chức năng tìm/thay thế của Excel, bạn không thể sử dụng các ký tự đặc biệt để thực hiện các thay thế rộng hơn (còn gọi là tìm kiếm ký tự đại diện). Điều này trở nên rõ ràng nếu bạn cố gắng sử dụng dấu hoa thị (*) như một phần của giá trị Tìm kiếm của mình. Đừng lo lắng, chúng tôi sẽ đề cập đến chức năng này trong phần tiếp theo vì Power Query xử lý ký tự đại diện có một chút khác biệt so với Excel Xem kích thước đầy đủ
Giải nén (Tìm/Thay thế Tìm kiếm Ký tự Đại diện)“Trích xuất” là cách Power Query thực hiện các hành động Tìm/Thay thế chuyên biệt hơn. Nếu bạn nhấp vào nút menu Trích xuất trong tab Chuyển đổi, bạn sẽ thấy nhiều hành động khác nhau mà bạn có thể thực hiện Trong truy vấn mẫu của chúng tôi, chúng tôi sẽ xem xét thực hiện các thao tác sau trong cột KHÁCH HÀNG để xóa tên khách hàng
Trong Excel, các kiểu dọn dẹp này sẽ sử dụng hộp thoại Tìm & Thay thế và đảm bảo nhu cầu sử dụng dấu hoa thị. Trong Power Query, việc làm sạch dữ liệu tương tự này có thể được thực hiện bằng cách sử dụng Văn bản trước Dấu phân cách, Văn bản sau Dấu phân cách và Văn bản giữa các Dấu phân cách Ví dụ của chúng tôi, chúng tôi muốn giữ văn bản trước dấu phân cách, vì vậy chúng tôi sẽ cần nhấp vào nút Văn bản trước dấu phân cách. Sau đó nhập ký tự phân cách hoặc chuỗi ký tự của bạn và nhấp vào OK Xem kích thước đầy đủ Sau khi tạo xong hai bước Trích xuất, bạn sẽ thấy ngay dữ liệu khách hàng thẳng hàng trong cột KHÁCH HÀNG Xem kích thước đầy đủ
Xóa cộtNhiều báo cáo chúng tôi sử dụng có các cột bổ sung không nhất thiết hữu ích cho chúng tôi. Điều này có nghĩa là phải xem qua và xóa chúng mỗi khi chúng tôi nhận được báo cáo mới May mắn thay, với Power Query, chúng tôi có thể tự động xóa các cột cụ thể dựa trên tên tiêu đề của chúng trong vài giây. Chỉ cần làm như sau để xóa một cột
Trong truy vấn mẫu của chúng tôi, chúng tôi sẽ xóa cột KHÁCH HÀNG KHÔNG vì số ID khách hàng không hữu ích cho chúng tôi trong khi chúng tôi đang phân tích dữ liệu của mình Bạn sẽ thấy bên dưới rằng cột KHÁCH HÀNG KHÔNG bị xóa khỏi khu vực xem trước truy vấn sau khi chúng tôi đã xóa nó Xem kích thước đầy đủ Nếu cần xóa nhiều cột, bạn có thể sử dụng phím ctrl hoặc phím shift để chọn nhiều cột trước khi nhấp vào nút Xóa cột (tương tự như cách bạn chọn nhiều cột trong Excel) Nếu bạn xóa riêng lẻ nhiều cột liên tiếp, Power Query sẽ sử dụng trí thông minh của nó để hợp nhất các thao tác xóa thành một bước duy nhất
Sắp xếp lại các cộtNếu bạn muốn di chuyển các cột xung quanh trong đầu ra truy vấn của mình, bạn có thể nhấp và kéo tiêu đề đến vị trí cột bạn muốn (lưu ý rằng bạn có thể chọn bội số và kéo bằng cách sử dụng phím ctrl hoặc phím shift). Nếu bạn muốn sử dụng nút Ribbon, bạn có thể làm như sau
Đối với truy vấn mẫu của chúng tôi, hãy di chuyển cột SẢN PHẨM vào giữa cột KHÁCH HÀNG và DOANH THU Xem kích thước đầy đủ Nếu bạn di chuyển từng cột ngược lại nhau, Power Query sẽ sử dụng trí thông minh của nó để hợp nhất các chuyển động đó thành một bước duy nhất
Xóa hàngViệc xóa các hàng trong Power Query đòi hỏi phải thay đổi tư duy so với Excel. Thay vì xóa các hàng theo cách vật lý, Power Query sử dụng tính năng lọc ra dựa trên tiêu chí Điều này có ý nghĩa nếu bạn nghĩ về những gì Power Query cuối cùng mang lại cho bạn. Khi bạn tải đầu ra của truy vấn trở lại bảng tính, bất kỳ thứ gì được lọc ra đơn giản là không được tải Lọc ra dữ liệu hoàn toàn giống như Excel. Chỉ cần nhấp vào biểu tượng mũi tên xuống bên cạnh tiêu đề cột và chọn cách bạn muốn lọc dữ liệu. Sau khi bạn nhấp vào nút OK, một bước sẽ được tạo cho hành động lọc Đối với truy vấn mẫu của chúng tôi, hãy lọc ra EDF Corp vì họ là công ty con của công ty chúng tôi và chúng tôi không muốn đưa doanh thu liên công ty của họ vào dữ liệu của mình Xem kích thước đầy đủ Là một giải pháp thay thế cho việc lọc, Power Query có một số hành động được định cấu hình trước để xóa các hàng. Nếu bạn điều hướng đến tab Trang chủ và mở menu Xóa hàng, bạn sẽ thấy nhiều hành động khác nhau mà bạn có thể thực hiện để xóa hàng Hãy nhớ rằng lỗi Ngày mà chúng tôi gặp phải khi định dạng kiểu dữ liệu cột (ngày 30 tháng 2 không tồn tại)? Xem kích thước đầy đủ Bây giờ chúng tôi biết dữ liệu của chúng tôi sẽ có ngày hợp pháp ở đầu ra để chúng tôi có thể tóm tắt rõ ràng dữ liệu của mình theo thời gian
Sắp xếpNẾU bạn muốn sắp xếp lại dữ liệu của mình theo thứ tự Tăng dần hoặc Giảm dần, bạn có thể thực hiện rất dễ dàng bằng cách
Đối với truy vấn mẫu của chúng tôi, hãy sắp xếp cột NGÀY theo thứ tự Tăng dần để ngày cũ nhất nằm ở đầu bảng Quản lý các bước tự động hóa của bạnKhi bạn thêm ngày càng nhiều bước vào truy vấn của mình, sẽ có khả năng xảy ra các tình huống mà bạn cần quản lý chúng một chút sau khi tạo Chỉnh sửa các bước cũKhi bạn chọn một Bước cụ thể, dữ liệu xem trước truy vấn sẽ hiển thị đầu ra của bước hiện tại đó. Điều này cho phép bạn di chuyển ngược/tiến trong thời gian thông qua tự động hóa của mình và xem kết quả một cách trực quan Ngoài ra, Thanh công thức sẽ hiển thị mã cho bước cụ thể đó. Rất hoan nghênh bạn sửa đổi mã trực tiếp trong Thanh công thức nếu cần (chỉ cần đảm bảo rằng bạn không có bất kỳ lỗi chính tả nào). Nhấp vào nút dấu kiểm bên cạnh thanh Công thức sau khi bạn hoàn tất để xác nhận thay đổi Xem kích thước đầy đủ Nhấp chuột phải vào Menu BướcHầu hết việc quản lý các Bước của bạn được thực hiện thông qua Menu Nhấp chuột phải. Nếu bạn nhấp chuột phải vào Bước bạn muốn sửa đổi, bạn sẽ thấy nhiều tùy chọn có sẵn. Trình đơn này sẽ cho phép bạn
Các bước lập tài liệuGhi lại rõ ràng mục đích của từng Bước là một thói quen rất tốt để bắt đầu. Rất có thể bạn sẽ có nhiều Bước được tạo trong truy vấn của mình và bạn sẽ muốn ngăn chặn mọi cơ hội quên lý do đằng sau một Bước nhất định. Ghi lại các bước của bạn cũng giúp người khác hiểu rõ hơn những gì đang diễn ra ở hậu trường Nơi tốt nhất để cung cấp mô tả Bước của bạn là thông qua hộp thoại Thuộc tính Bước. Hộp thoại này được truy cập bằng cách kích chuột phải vào tên Step và chọn Properties Trong hộp thoại Thuộc tính Bước, bạn có thể đổi tên Bước của mình để có ý nghĩa hơn và cũng cung cấp mô tả. Tôi luôn cố gắng đưa hành động mà Bước đang thực hiện và cả lý do đằng sau hành động đó vào phần mô tả của tôi. Ý tưởng là để nhanh chóng nhớ lại lý do tại sao Bước được tạo ngay từ đầu Khi bạn thêm mô tả vào một Bước, một biểu tượng thông tin sẽ xuất hiện bên cạnh tên Bước trong hộp danh sách Các bước đã áp dụng. Di chuột qua biểu tượng thông tin sẽ hiển thị mô tả Bước của bạn trong chú giải công cụ Viết/Chỉnh sửa Công thức Tùy chỉnhPower Query sử dụng ngôn ngữ công thức riêng được gọi đơn giản là “M”. Mặc dù có nhiều loại hàm giống như Excel nhưng tên hàm và thứ tự nhập có thể khác. Ví dụ: phiên bản Power Query của hàm Right() trong Excel là Văn bản. Kết thúc Vì đây là hướng dẫn bắt đầu nhanh nên rõ ràng là tôi sẽ không dạy bạn cách viết mã bằng M, nhưng tôi muốn chia sẻ với bạn cấu trúc về cách viết mã để bạn có thể thực hiện các trình soạn thảo cơ bản và hiểu rõ hơn về các giải pháp mà bạn có thể áp dụng. Nếu bạn muốn tìm hiểu thêm về các chức năng khác nhau có sẵn trong ngôn ngữ M, bạn có thể truy cập trang web Thư viện chức năng ngôn ngữ M chính thức của Power Query Một lưu ý cuối cùng trước khi chúng ta bắt đầu vì điều này rất quan trọng cần ghi nhớ. Ngôn ngữ Power Query M phân biệt chữ hoa chữ thường. Điều này có nghĩa là “a” không giống như “A”. Hoặc trong trường hợp chức năng “văn bản. kết thúc” sẽ báo lỗi vì nó không được viết “Văn bản. Kết thúc". Điều này khiến tôi khó nhớ vì ngôn ngữ VBA của Excel không phân biệt chữ hoa chữ thường Chúng tôi sẽ bắt đầu bằng cách mở Trình chỉnh sửa nâng cao và xem lại mã mà Power Query đã viết cho chúng tôi trong truy vấn mẫu của chúng tôi. Hãy nhớ rằng bạn có thể truy cập Advanced Editor thông qua tab Home của Power Query Editor Xem kích thước đầy đủ Cấu trúc mã mức cao của một truy vấn trong mã M như sau
Xem kích thước đầy đủ Nhấp để phóng to Bạn sẽ lưu ý rằng trong hình trên tôi đã đánh dấu bằng màu vàng một thực tế là mọi Bước đều tham chiếu đến tên của Bước trước đó trong công thức của nó. Đây là cách mã duy trì thứ tự thực hiện các Bước. Bạn sẽ cần phải hết sức chú ý đến điều này nếu bạn đang đổi tên các Bước hoặc di chuyển các bước xung quanh trong Trình chỉnh sửa Nâng cao. Cá nhân tôi thực hiện tất cả việc duy trì tên, thứ tự và mô tả của Bước bằng cách sử dụng hộp danh sách Các bước đã áp dụng nên tôi không phải lo lắng về lỗi chính tả Đó là tất cả những gì chúng ta sẽ đi trong hướng dẫn này liên quan đến viết mã. Hy vọng rằng điều này sẽ giúp bạn hiểu rõ về những điều cần chú ý và nơi cần đến nếu bạn cần viết mã thủ công một thứ gì đó trong Thanh công thức hoặc Trình chỉnh sửa nâng cao Xuất dữ liệu của bạn trở lại ExcelKhi bạn đã hoàn thành tất cả các bước của mình, bạn sẽ sẵn sàng xuất dữ liệu của mình trở lại Excel. Bạn có thể làm điều này bằng cách
Nếu đây là lần đầu tiên bạn tải truy vấn vào bảng tính, một trang tính mới sẽ được tạo với tên trang tính được đặt thành tên truy vấn Dữ liệu cũng sẽ được lưu trữ trong một đối tượng Bảng Excel Bạn cũng sẽ thấy một tab theo ngữ cảnh mới có tên là Truy vấn bất cứ khi nào bạn bấm vào một bảng Excel được kết nối với một truy vấn từ Power Query. Tab này sẽ cung cấp cho bạn một số tùy chọn có thể giúp bạn dễ dàng duy trì truy vấn trong tương lai. Đáng chú ý nhất là nút Chỉnh sửa mở ra Power Query Editor Làm mới Tự động hóa Power Query của bạnBây giờ bạn đã thiết lập xong tất cả các bước Power Query của mình, bạn có thể tận hưởng thời gian tiết kiệm được trong tương lai. Sau khi bạn đã đưa dữ liệu của mình vào tệp Excel hoặc nguồn dữ liệu bạn đang kết nối đã được cập nhật, bạn chỉ cần nhấp vào nút làm mới để bắt đầu quá trình tự động hóa của mình Trong khi truy vấn của bạn đang được làm mới, bạn sẽ thấy một thông báo trên Thanh trạng thái (dưới cùng bên trái của cửa sổ Excel). Bạn có thể tiếp tục làm việc trên các phần khác của sổ làm việc Excel trong khi truy vấn của bạn cập nhật Có một số cách khác nhau để bạn có thể truy cập vào nút Làm mới, vì vậy tôi sẽ trình bày tất cả các cách đó bên dưới. Tất cả đều làm chính xác như nhau, vì vậy việc bạn sử dụng phương pháp nào thực sự là sở thích cá nhân. Phương pháp ruy băng
Phương pháp nhấp chuột phải
Truy vấn & Phương thức kết nối
Phương pháp Power Query Editor
Sách truy vấn sức mạnh tốt nhấtBan đầu, tôi học cách sử dụng Power Query bằng cách mua một cuốn sách tóm tắt mọi thứ về công cụ này. Trước đó, không có nguồn tài nguyên tập trung tuyệt vời nào nên một cuốn sách là cách tốt nhất để hiểu rõ về công cụ này. Tôi vẫn nghĩ rằng đọc qua một cuốn sách là một cách tuyệt vời để phát triển nền tảng tốt cho các kỹ năng Power Query Cuốn sách yêu thích cá nhân của tôi là của Miguel Escobar và Ken Puls. Ban đầu họ viết M là dành cho Data Monkey, đây là cuốn sách tôi học được từ. Vào năm 2021, họ đã phát hành Làm chủ dữ liệu của bạn bằng Power Query trong Excel và Power BI, đây là phiên bản cập nhật của cuốn sách gốc của họ Dưới đây là một số liên kết của Amazon tới một số sách liên quan đến Power Query hàng đầu hiện có Lưu ý, tôi nhận được một khoản hoa hồng nhỏ từ Amazon nếu bạn mua một cuốn sách hoặc bất kỳ thứ gì khác từ Amazon bằng cách sử dụng các liên kết sách ở trên trong vòng 24 giờ sau khi nhấp vào (bạn không phải trả thêm phí). Tôi đánh giá cao việc bạn hỗ trợ trang web này nếu bạn chọn sử dụng các liên kết liên kết Amazon của tôi Các bài viết Power Query bổ sung mà bạn có thể học hỏiDưới đây là một số bài viết Power Query bổ sung mà bạn có thể quan tâm
Tôi hy vọng điều này đã giúpHy vọng rằng tôi có thể cung cấp cho bạn một nền tảng vững chắc để bạn hiểu mục đích của Power Query và có thể bắt đầu khám phá nhiều điểm mạnh mà nó mang lại. Tôi muốn đây là phần giới thiệu tốt nhất về Power Query mà tôi có thể vì tôi nghĩ rằng đây là một công cụ rất quan trọng trong Excel nhưng lại bị bỏ qua quá nhiều. Nếu bạn có bất kỳ câu hỏi nào về một phần nhất định hoặc đề xuất về cách cải thiện hướng dẫn này, vui lòng cho tôi biết trong phần nhận xét bên dưới Thông tin về các Tác giảchào bạn. Tôi là Chris và tôi điều hành trang web TheSpreadsheetGuru khi rảnh rỗi. Ban ngày, tôi thực sự là một chuyên gia tài chính phụ thuộc khá nhiều vào Microsoft Excel trong thế giới doanh nghiệp. Tôi thích lấy những điều tôi học được trong “thế giới thực” và chia sẻ chúng với mọi người ở đây trên trang web này để bạn cũng có thể trở thành chuyên gia bảng tính tại công ty của mình Qua nhiều năm làm việc trong thế giới doanh nghiệp, tôi đã có thể nắm bắt các cơ hội để làm việc với Excel tốt hơn và đã xây dựng nhiều phần bổ trợ Excel khác nhau, từ việc chèn ký hiệu dấu tích đến tự động sao chép/dán từ Excel sang PowerPoint. Nếu bạn muốn cập nhật những tin tức mới nhất về Excel và nhận trực tiếp các mẹo Excel có ý nghĩa nhất mà tôi đã học được qua email trong nhiều năm, bạn có thể đăng ký nhận bản tin miễn phí của tôi. Tôi hy vọng tôi có thể cung cấp cho bạn một số giá trị ngày hôm nay và hy vọng sẽ sớm gặp lại bạn ở đây. - Chris Power Query khác với Excel như thế nào?Power BI có thể kết nối với một số lượng lớn nguồn dữ liệu, trong khi khả năng kết nối của Excel bị hạn chế . Ngoài ra, không giống như Excel, Power BI có thể dễ dàng sử dụng từ thiết bị di động. Power BI có tốc độ xử lý nhanh hơn Excel. Bảng điều khiển Power BI hấp dẫn, tương tác và tùy chỉnh trực quan hơn so với trong Excel.
Power Query giải quyết vấn đề gì?Power Query có thể giải quyết tất cả các loại vấn đề cho người dùng Excel đang vật lộn với dữ liệu. Hợp nhất, Tách, Hợp nhất, Thay thế, Lọc, "UNPIVOTTING. ". danh sách này là toàn diện và dường như không ngừng phát triển |
