Power Query – Nhập dữ liệu

Hôm nay Học Excel Online sẽ bắt đầu với  nhập dữ liệu Power Query. Đây là bước đầu tiên và cơ bản nhất khi học Power Query, nên bạn nên nắm thật kỹ để có thể linh hoạt trong các thao tác khác.

Bài viết này gồm hai phần:

• Sử dụng dữ liệu trong cùng một cửa sổ làm việc
• Sử dụng dữ liệu từ các tệp bên ngoài và cửa sổ làm việc bên ngoài.

Thông qua các ví dụ, tôi hy vọng sẽ giúp bạn đưa dữ liệu vào Power Query một cách dễ dàng.

Xin lưu ý rằng Excel và Power Query liên tục được cập nhật bởi Microsoft. Tùy thuộc vào các phiên bản của Excel thì tên, vị trí và tùy chọn của các hành động khác nhau có thể hơi khác so với các ví dụ trong bài viết này.

Sử dụng dữ liệu trong cùng một workbook

Để sử dụng dữ liệu từ trong cùng một cửa sổ làm việc, Power Query cần biết khu vực của dữ liệu nguồn.

Có hai định dạng vùng dữ liệu mà Power Query xử lý tốt đó là Bảng Excel và các dải ô đã đặt tên. Các ví dụ dưới đây cho thấy cách sử dụng cả hai phương pháp này.

Nhập dữ liệu từ Bảng

Đầu tiên, điều quan trọng cần lưu ý là việc sử dụng “Tables” để chỉ cụ thể tính năng Bảng Excel. Bảng là một yếu tố quan trọng trong việc lưu trữ dữ liệu của Excel. Đầu ra từ Power Query thường sẽ ở định dạng Bảng. Vì vậy, Bảng là nền tảng để học Power Query tốt.  Nếu bạn chưa quen với Tables, tôi khuyên bạn nên dành thời gian tìm hiểu và sử dụng chúng.

Ví dụ 1:

Chọn bất kỳ ô nào trong Bảng. Sau đó bấm From Table/Range từ phần Get & Transform Data của mục Data

Cửa sổ Power Query sẽ mở ra. Vì đây là giao diện đầu tiên và cơ bản nhất nên tôi sẽ dành một chút thời gian để tìm hiểu từng chi tiết, làm quen để học Power Query dễ dàng

The Ribbon – Tương tự như Excel, Ribbon chứa các lệnh chính được nhóm thành các tab riêng biệt. Chúng ta sẽ sử dụng Ribbon trong suốt loạt bài này, vì vậy bạn sẽ nhận ra các lệnh phổ biến nhất trong khi sử dụng.

Properties – Đây là nơi chúng ta đặt tên cho truy vấn(query). Điều quan trọng là đặt tên có ý nghĩa cho truy vấn của bạn để bạn biết nó làm gì mà không cần phải xem lại các bước.

Applied steps – Giống như trình ghi macro, mỗi hành động bạn thực hiện sẽ được ghi lại trong các bước đã áp dụng. Có thể thêm, bớt và chỉnh sửa các bước.

Data preview – Khu vực này hiển thị bản xem trước dữ liệu, dựa trên bước áp dụng đã chọn. Bằng cách nhấp chuột phải vào các tiêu đề cột, chúng ta có thể truy cập vào nhiều công cụ chuyển đổi dữ liệu cũng được tìm thấy trên Ribbon.

Hiểu các bước áp dụng

Trong cửa sổ các bước được áp dụng đã chứa một danh sách nhỏ các mục. Các bước này là nỗ lực của Excel để tự động chuyển đổi dữ liệu dựa trên những gì nó hiểu rằng điều ta cần làm.

Source – Xác định dữ liệu nguồn (ví dụ: Bảng trên trang tính)

Changed Type – Excel đã phân tích dữ liệu và cố gắng áp dụng kiểu dữ liệu chính xác cho mỗi cột.

Các bước này đã được áp dụng tự động bởi Excel. Tuy nhiên các bước tự động không phải lúc nào cũng đúng, vì vậy bạn có thể phải thực hiện lại chúng. Tôi nhận thấy bước Change Type thường có thể gây ra lỗi khó chịu về sau, rất khó tìm. Đôi khi việc xóa bước Change Type và thực hiện theo cách thủ công sẽ dễ dàng hơn.

Khi chúng ta thực hiện nhiều thao tác hơn để chuyển đổi dữ liệu, các bước này sẽ được thêm vào danh sách các bước được áp dụng.

Nhấp vào từng bước để xem từng bước chuyển đổi dữ liệu đang được áp dụng.

Mã M

Mã M là ngôn ngữ mà Power Query sử dụng để ghi lại các bước được áp dụng, là ngôn ngữ mà người học Power Query cần chú ý. 

Hãy cùng xem qua mã M để xem Power Query đã ghi lại các bước như thế nào. Chúng ta sẽ không dành nhiều thời gian để xem mã M, nhưng nó sẽ giúp hiểu cách học Power Query và cách thực hiện các chỉnh sửa nâng cao hơn cho các truy vấn ở giai đoạn sau.

Nhấp vào: Home -> Advanced Editor

Cửa sổ Advanced Editor sẽ xuất hiện.

Hãy xem xét các mã và so sánh nó với các bước áp dụng mà chúng ta đã có.

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

Dòng trên là xác định sử dụng Bảng Excel nào làm nguồn. Nếu chúng ta muốn thay đổi Truy vấn để xem một Bảng khác, chúng ta có thể thay đổi tblSalesData thành tên của Bảng mới.

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type datetime}, {“Customer”, type text}…….

Dòng trên là thay đổi kiểu dữ liệu. Ví dụ: cột “Date” đã được thay đổi thành loại Date/Time. Thay đổi này dựa trên đánh giá của Power Query về dữ liệu trong cột

Thêm một số bước

Bây giờ chúng ta sẽ thực hiện một số phép biến đổi cơ bản và xem cách chúng được thêm vào danh sách các bước được áp dụng.

Nhấp đúp vào cột “Value” và đổi tên thành “Amount”.

Nhấp vào biểu tượng Data/Time và thay đổi nó thành Date.

Cột Date sẽ chỉ hiển thị ngày chứ không hiển thị ngày và giờ.

Bây giờ nhìn vào cửa sổ Các bước được áp dụng( Applied Steps), có hai bước mới được thêm vào cho các hành động mà chúng ta đã hoàn thành.

Học Power Query thì không thể không biết thao tác này

Chỉnh sửa các bước

Để xóa một bước, chúng ta có thể nhấp vào biểu tượng X bên cạnh bước đó.

Để biết thêm các thay đổi, bạn có thể nhấp chuột phải để xem tất cả các tùy chọn.

Các tùy chọn này đều có phần hướng dẫn. Nếu có bất kỳ điều gì bạn không hiểu, hãy thử chúng trên một số dữ liệu mẫu…có thể điều tồi tệ nhất sẽ xảy ra 🙂

Đổi tên có lẽ là lệnh hữu ích nhất ở đây và được sử dụng ít nhất. Khi các bước dài hơn, có thể khó nhớ mỗi bước làm gì. Bạn nên đổi tên các bước khi truy vấn vượt quá một vài bước (xem ảnh chụp màn hình bên dưới).

Tải lại dữ liệu vào Excel

Để hoàn tất quá trình. Chúng ta sẽ tải lại dữ liệu vào sổ làm việc Excel.

Nhấp vào: Home -> Close & Load

Một trang tính mới sẽ được tạo, với một Bảng hiển thị dữ liệu đã chuyển đổi.

Có vẻ những bước này không quá khó với bạn. Tất cả những gì chúng ta đã làm là tải một Bảng từ Excel vào Power Query, thực hiện một vài thay đổi nhỏ, sau đó tải lại dữ liệu đó vào Excel. Hãy nhớ rằng, đây là một ví dụ đơn giản để cho bạn thấy các thao tác cơ bản của công cụ trong khi học Power Query. Khi chúng ta chuyển qua loạt bài này, chúng ta sẽ biến những bước đơn giản này thành một thứ gì đó tuyệt vời…. Tôi hứa 🙂

Nhập dữ liệu từ phạm vi Tiêu chuẩn

Nếu bạn chọn một phạm vi tiêu chuẩn (ví dụ: Ô A4: E54), thay vì một Bảng, Excel sẽ tự động thay đổi các ô đó thành Bảng trước khi nhập. Mọi thứ khác sau thời điểm đó đều giống với các bước trên.

Nhập dữ liệu từ các dải ô đã đặt tên

Có một cách khác để cho người học Power Query biết phạm vi ô sẽ được nhập bằng cách sử dụng các dải ô đã đặt tên.

Ví dụ 2 – Đã đặt tên Range.xlsx từ các bản tải xuống.

Cách dễ nhất để tạo phạm vi đã đặt tên là chọn select all the cells, sau đó nhập tên vào Name Box và nhấn  Return.

Trong ví dụ, một dải ô có tên là SalesData đã được tạo.

Hãy nhập dữ liệu trong phạm vi được đặt tên vào Power Query. Các bước thực hiện rất giống với việc nhập dữ liệu từ Bảng (do đó tôi sẽ không lặp lại tất cả các bước ở trên, nhưng tôi sẽ nêu rõ những điểm khác biệt).

Chọn phạm vi được đặt tên từ danh sách Name Box thả xuống

Phạm vi ô sẽ được chọn trên trang tính.

Bấm From Table/Range từ phần Get & Transform Data của mục Data

Dữ liệu được nhập sẽ trông tương tự như khi sử dụng Bảng. Tuy nhiên, có thể có một số bước bổ sung được áp dụng.

Trong ví dụ này, một bước bổ sung của Promote Headers đã được thêm vào – chúng ta sẽ xem xét điều này ở phần sau của bài viết này. Nhấp qua các bước đã áp dụng để xem những thay đổi nào đã được áp dụng nhé!

Biểu tượng bánh răng

Bạn có thấy biểu tượng bánh răng nhỏ bên cạnh bước Promoted Headers không? Điều này cho biết nơi Power Query cho phép chúng ta thay đổi cài đặt. Hãy nốt lại trong vở học Power Query của mình ngay nhé.

Nhấp vào biểu tượng bánh răng.

Cửa sổ Use First Row as Headers sẽ mở ra. Cửa sổ mở ra sẽ khác nhau tùy thuộc vào loại bước.

Trong ví dụ của chúng ta, chúng ta vẫn muốn tùy chọn Promote all scalar types, vì vậy chúng ta có thể nhấp vào OK mà không thực hiện bất kỳ thay đổi nào.

Hiển thị thanh công thức

Thay vì xem xét tất cả Mã M trong Trình chỉnh sửa nâng cao(Advanced Editor) như chúng ta đã làm trước đây, chúng ta có thể hiển thị Thanh công thức(Formula Bar.)

Nhấp:  View -> Formula Bar

Khi chúng ta nhấp qua các bước, thanh công thức sẽ thay đổi để hiển thị Mã M được sử dụng cho mỗi bước. Bạn không cần phải thay đổi điều này, nhưng lưu ý rằng nó vẫn ở đó để sử dụng cho quá trình học Power Query ở tương lai.

Nên sử dụng Bảng hay Dải ô được đặt tên?

Người học Power Query thường xem xét hai lựa chọn, cái nào là tốt nhất? Không có câu trả lời thẳng thắn, vì nó phụ thuộc vào dữ liệu nguồn của bạn.

Bảng

• Có thể tự động mở rộng
• Phải ở định dạng dữ liệu có cấu trúc
• Phải có một hàng tiêu đề xác định
• Không thể có các phép tính trong hàng tiêu đề

Dải ô được đặt tên

• Không tự động mở rộng theo mặc định
• Có thể chứa bất kỳ số lượng hàng tiêu đề nào (mặc dù sẽ có nhiều biến đổi hơn trong Power Query)
• Có thể có các phép tính trong hàng tiêu đề

Phạm vi được đặt tên động cũng có thể được nhập vào Power Query, nhưng điều đó quá nâng cao đối với loạt bài giới thiệu này.

Lời khuyên của tôi cho bạn học  Power Query là hãy luôn chọn Table, trừ khi có lý do cụ thể để không chọn.

Sử dụng dữ liệu từ các tệp bên ngoài và workbook

Sau khi xem xét dữ liệu được lưu trữ trong cùng một workbook với truy vấn, chúng ta hãy chuyển sự chú ý của chúng ta đến dữ liệu được lưu trữ bên ngoài workbook.

Khi học Power Query chúng ta thường mở tệp CSV, tệp văn bản hoặc workbook Excel và đưa dữ liệu vào một sổ làm việc khác. Bằng cách nhập dữ liệu trực tiếp vào sổ làm việc bằng Power Query, điều đó có nghĩa là chúng ta không bao giờ cần coppy và paste 🙂 Liên kết đến dữ liệu nguồn được duy trì và nó có thể được làm mới bằng cách nhấp vào một nút (xem thêm trong phần tiếp theo phần).

Tệp CSV

Để học Power Query tốt ban cần biết đến CSV. Đây là một định dạng tệp rất phổ biến để xuất ra từ ​​các hệ thống khác. Tin tốt là Power Query yêu thích các tệp CSV.

Ví dụ 3 – CSV.csv từ các bản tải xuống.

Từ Data ribbon, Nhấp vào Get Data -> From File -> From Text/CSV

Cửa sổ Nhập dữ liệu(Import Data) sẽ mở ra. Điều hướng đến tệp CSV, chọn nó và nhấp vào Import.

Power Query sẽ mở một cửa sổ mới và hiển thị một mẫu dữ liệu.

Các tệp CSV vốn được phân cách bằng dấu phẩy, vì vậy không cần thay đổi cài đặt.

Để tải dữ liệu trực tiếp vào Excel mà không có bất kỳ thay đổi nào, chúng ta có thể nhấp vào Load, hoặc để chuyển đổi dữ liệu, hãy nhấp vào Transform. Tất cả các bước từ bây giờ sẽ giống như chúng ta đã thấy trong các phần trên.

Tệp văn bản

Tệp văn bản cũng tương tự như CSV, nhưng có thể có nhiều định dạng dữ liệu. Hãy xem tệp Văn bản được phân tách bằng tab để biết nó khác nhau như thế nào.

Ví dụ 4:

Làm theo các bước tương tự như đối với tệp CSV. Tuy nhiên, thay vì chọn dấu phẩy làm dấu phân cách, hãy chọn tab.

Nếu dữ liệu bạn đang nhập ở định dạng văn bản khác, hãy chọn tùy chọn thích hợp từ danh sách thả xuống.

Sau đó nhấp vào Load hoặc Transform Data tùy nhu cầu.

Worksheet(Bảng tính)

Cuối cùng, đối với phần này, chúng ta sẽ nhập nội dung của tệp Excel.

Ví dụ 5:.

Từ Data ribbon, nhấp Get Data -> From File -> Excel Workbook

Chọn worksheet trong Excel từ cửa sổ Import Data  và bấm Import.

Cửa sổ Navigator sẽ mở ra. Đối với ví dụ này, chỉ cần chọn một trang tính (ví dụ: tháng 1) và nhấp vào Transform. Chúng ta sẽ cùng tìm hiểu việc kết hợp nhiều tệp trong một bài đăng trong tương lai.

Bây giờ sẽ đến với trường hợp mà chúng ta chưa từng gặp trước đây, nhưng đó là một kịch bản có thể xảy ra trong nhiều quá trình học Power Query…. Excel không biết hàng nào chứa tiêu đề. Nhìn vào ảnh chụp màn hình bên dưới, Excel đã giả định rằng hàng 1 của bảng tính có chứa các tiêu đề, nhưng theo các ví dụ trước, các tiêu đề phải là Date, Customer, Product, Sold By và Value, nằm trong hàng 4.

Nhưng đừng lo lắng, chúng ta có thể khắc phục điều này dễ dàng với một số biến đổi đơn giản.

Hãy xóa 2 hàng trên cùng. Nhấp vào: Home -> Remove Rows -> Remove Top Rows

Cửa sổ Remove Top Rows sẽ mở ra. Trong ví dụ này, chúng ta muốn loại bỏ 2 hàng, sau đó nhấp vào OK.

Tiếp theo, chúng ta có thể đẩy hàng trên cùng mới làm hàng tiêu đề. Nhấp vào: Home -> Use First Row as Headers

Vậy là bài học Power Query đầu tiên đã kết thúc. Bạn đã có thể nhập dữ liệu thuần thục chưa? Nếu còn bấ cứ thắc mắc nào về Power query nữa thì đừng ngại ngần cmt ở phía dưới vì Học Excel Online sẽ nhanh chóng giải đáp giúp bạn.