Nhập dữ liệu và thực hiện các thay đổi cơ bản trong Microsoft Power Query là một cách hay để tạo tập lệnh và bắt đầu tìm hiểu cách chúng hoạt động. Cùng FUNiX từng bước tạo tập lệnh, thực hiện các thay đổi và xem kết quả.
Ngôn ngữ “M”
Trước khi đi sâu vào, hãy tìm hiểu một chút về ngôn ngữ chạy Power Query. Microsoft đã tạo ngôn ngữ “M” để xây dựng Power Query.
Nếu bạn không phải là lập trình viên, đừng lo lắng. Bạn không cần biết cách viết code bằng Ngôn ngữ “M” ngay lập tức, nhưng sẽ rất hữu ích nếu bạn biết định dạng của nó khi bạn bắt đầu tìm hiểu về Power Query.
Định dạng ngôn ngữ “M” cơ bản
Ở dạng cơ bản nhất, ngôn ngữ “M” được cấu trúc như một “câu lệnh cho phép” (let-in statement), như sau:
Trong tập lệnh Power Query, let đặt code bạn muốn chạy (đầu vào) và in là nơi bạn muốn chạy code đó (đầu ra). Code được chạy bên trong câu lệnh let có thể được tạo thành từ các biến, biểu thức và giá trị.
Các biến có thể được đặt tên theo hai cách. Đầu tiên là với các từ và không có khoảng trắng. Ví dụ: “Variable” (biến) hoặc “MyVariable”.
Nếu bạn muốn sử dụng khoảng trắng trong tên biến của mình, chỉ cần bắt đầu bằng một #.
. Ví dụ: #”My Variable” hoặc #”New Variable”. Mỗi biến này có một khoảng trắng trong tên nhưng vẫn hợp lệ.
Giờ hãy bắt đầu với tập lệnh Power Query của chúng ta.
Nhập dữ liệu vào Power Query
Trong ví dụ này, hãy lấy một bảng mà chúng tôi tạo có tên Fruits.xlsx hiển thị các loại trái cây khác nhau và giá của chúng. Có một số cột đơn giản cho fruit (hoa quả), city (thành phố) và price (giá)
Mục tiêu của hướng dẫn này là lấy cột Price và chuyển đổi dữ liệu từ số sang định dạng tiền tệ bằng Power Query.
Chúng tôi đã lưu bảng này vào desktop. Hãy tạo một sổ làm việc Excel mới có tên Fruit Data.xlsx để nhập dữ liệu của chúng ta.
Trên thanh công cụ chọn Data và nhấn vào Get Data. Điều này sẽ cung cấp cho bạn các tùy chọn để chọn nguồn dữ liệu của mình, chúng tôi muốn nó From File > From Workbook.
Thao tác này sẽ mở trình khám phá tệp của bạn. Hãy tiếp tục và chọn sổ làm việc Fruits.xlsx để nhập và nhấp vào OK.
Sử dụng Power Query Editor
Sau khi bạn bấm OK, Power Query Navigator sẽ bật lên để chọn dữ liệu trong sổ làm việc. Chọn bảng Fruit từ sổ làm việc, bản xem trước của dữ liệu bạn chọn sẽ xuất hiện ở bên phải để xác nhận lựa chọn của bạn.
Bấm vào Transform Data (Chuyển đổi dữ liệu) để mở Power Query Editor.
Xem tập lệnh
Thoạt nhìn thì có rất nhiều điều đang diễn ra nhưng hãy tập trung vào việc lấy tập lệnh.
Khi Power Query Editor mở, trên thanh công cụ của bạn bên dưới Home, hãy nhấp vào Advanced Editor (Trình soạn thảo nâng cao) để mở tập lệnh. Trình soạn thảo nâng cao sẽ mở ra và bên trong bạn sẽ thấy tập lệnh Power Query bằng Ngôn ngữ M.
Nội dung có vẻ dài nên hãy chia nhỏ tập lệnh trước khi thực hiện thay đổi.
Nội dung tập lệnh
Trước hết, hãy lưu ý rằng tập lệnh có định dạng “let/in” mà chúng ta đã thực hiện với Ngôn ngữ M. Bên trong “let” là các dòng code thực hiện các thay đổi của bạn và code “in” là nơi các thay đổi sẽ được xuất ra.
Chúng ta hãy đi từng dòng một để cảm nhận rõ hơn về code.
letSource = Excel.Workbook(File.Contents("C:\Fruit.xlsx"), null, true),
Fruit_Sheet = Source{[Item="Fruit",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Fruit_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fruit", type text}, {"City", type text}, {"Price", type number}})
in
#"Changed Type"Phân tích tập lệnh
Code bắt đầu bằng một câu lệnh let, câu lệnh này bắt đầu mọi khối code. Điều đầu tiên Power Query sẽ làm là nhập tệp vào một biến có tên Source, đây đơn giản là tệp Fruit.xlsx mà chúng tôi đã chọn để nhập.
Lưu ý dấu phẩy ở cuối. Mỗi câu lệnh sẽ kết thúc bằng dấu phẩy, dấu phẩy này chỉ cho mã biết dòng tiếp theo.
Từ đó, Power Query lấy trang tính từ Fruit.xlsx và đặt nó thành một biến có tên Fruit_Sheet.
Dòng thứ ba đang sử dụng một hàm
Table.PromoteHeadersđể lấy các tiêu đề cột mà chúng ta đang sử dụng trong Excel và đặt chúng làm tiêu đề trong Power Query.
Dòng tiếp theo bắt đầu bằng
#"Changed Type",Power Query đang thay đổi loại dữ liệu của từng cột. Các cột
Fruit
được thay đổi thành
type text,
"City"thành
type text, Và
"Price"thành
type number. Power Query rất thông minh và cố gắng xác định loại dữ liệu nào được lưu trữ trong bảng.
Cuối cùng, câu lệnh in xuất code cho trình soạn thảo của chúng ta, bằng cách xuất mã đó sang biến cuối cùng, nó có thể nhận ra đó là
#"Changed Type".
Điều quan trọng cần lưu ý là: Mỗi biến được tạo trên một dòng sẽ được sử dụng theo cách nào đó trên dòng tiếp theo. Hãy nghĩ về nó giống như một chuỗi trong đó mọi mắt xích đều được kết nối với mắt xích cuối cùng.
Chỉnh sửa truy vấn
Bây giờ tập lệnh đã được xem và chúng tôi đã xem lại mã, hãy thực hiện chỉnh sửa. Hãy nhớ rằng, mục tiêu là lấy tất cả dữ liệu trong cột Price và thay đổi nó thành tiền tệ.
May mắn thay, chúng tôi đã có ý tưởng về cách thực hiện điều này. Nhớ lại dòng code sau:
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fruit", type text}, {"City", type text}, {"Price", type number}})Chúng ta có code đã thay đổi kiểu dữ liệu cho toàn bộ bảng! Đây là nơi chúng ta có thể thực hiện điều chỉnh và nhận được kết quả.
Mỗi cột được gán từng loại một, vì vậy hãy thực hiện thay đổi trực tiếp trong mã. Hiện tại, cột
"Price"được chỉ định
type number. Chúng ta muốn nó là tiền tệ, vì vậy hãy thay đổi code này thành
Currency.Type. Nó sẽ trông như sau:
Nhấn Done để quay lại màn hình chính. Ký hiệu loại bây giờ là ký hiệu đô la, có nghĩa là loại đã được chuyển đổi thành tiền tệ.
Phân tích dữ liệu tốt hơn trong Microsoft Excel
Trên đây chỉ là sơ lược về các tập lệnh Power Query bằng Ngôn ngữ M. Với nhiều sức mạnh hơn để làm việc với Excel, việc chuyển đổi dữ liệu sang bảng tổng hợp để sử dụng cho việc phân tích dữ liệu hoặc tạo biểu đồ trong Excel trở nên dễ dàng hơn bao giờ hết .
Tìm hiểu ngay chương trình học công nghệ thông tin trực tuyến tại FUNiX ở đây:
Vân Nguyễn
Dịch từ: https://www.makeuseof.com/tag/first-microsoft-power-query-script/
Bình luận (0
)