Trong bài viết này, Học Excel Online sẽ gợi ý cách chuẩn chỉnh hoá dữ liệu, có thể nói là đưa dữ liệu về dạng “Chuẩn” với nguyên tắc Power Query vào Excel. Bạn đang xem: Hướng dẫn sử dụng power bi
Chuẩn bị dữ liệu tốt chắc rằng là nhân tố thành công đặc biệt nhất trong phân tích dữ liệu. Nếu bố cục tổng quan dữ liệu ko đúng, các bạn sẽ tiếp tục đánh nhau với Excel; buộc phải sử dụng các công thức Excel nâng cao, hết sức phức tạp; thêm các cột không cần thiết hoặc thậm chí sao chép dữ liệu,….
Nhưng nếu dữ liệu nằm trong cấu trúc phù hợp, Excel đang trở đề nghị dễ dàng. Hãy thuộc xem Power Query để giúp gì cho họ trong trường hòa hợp này qua một số ví dụ trong nội dung bài viết này của Học Excel Online.
Xem nhanh
Ví dụ 1
Ở đây, mình có bảng dữ liệu như sau:
Bạn rất có thể thấy, dữ liệu gốc hiển thị một cột riêng cho từng quý. Tuy nhiên, thay vì có một cột cho mỗi quý thì tại sao ta không đưa các quý đó vào 1 cột riêng với đặt tên tiêu đề cột là Quý. Đây là cách bố trí dữ liệu mà lại Học Excel Online sẽ cố tiến hành trong bài viết này – Một cách bố trí dữ liệu “Chuẩn”.
Vậy làm ra sao để ta hoàn toàn có thể thiết kế lại tài liệu như đã mô tả trên?
Bạn đang làm điều ấy trong Excel như vậy nào?. Bạn sẽ Copy với Paste một cách thủ công bằng tay và rất giản đơn sai sót, nhầm lẫn,…và các lần dữ liệu gốc biến đổi bạn lại Copy lại???. Hay là bạn sẽ xử lý nó với năng lực VBA mà lại mình có???
KHÔNG, tránh việc một 1 chút nào nếu như bạn đã sở hữu công nạm Power Query thì hãy để nó xử lý việc đó cho mình một phương pháp rất nhanh chóng và hiệu quả.
Đầu tiên bạn nên đưa dữ liệu trên về dạng Table (Ctrl + T or Ctrl + L) với đặt tên đến Table nó 1 cái tên (ở đây mình đã đặt tên là VD_1). Power Query hỗ trợ cho chính mình khá nhiều kiểu để Input tài liệu vào như Table, Name range (Define name), Name động hay thậm chí bạn chỉ việc quét lựa chọn một vùng thì nguồn Query vẫn nhận vùng đó để lấy vào nguồn Query Editor. Tuy nhiên, với tài liệu được rước từ Excel thì tốt nhất có thể là bạn nên tạo Table cho nó.
Tiếp theo, bạn chỉ cần chọn một ô bất kỳ trong bảng kia vào chọn From Table/Range
Lúc này, hành lang cửa số Power Query Editor vẫn mở lên
Ở mục Applied Steps, chúng ta có thể thấy bao gồm 2 bước:
Source: Đây là nguồn tài liệu mà các bạn Input vào Power Query (trong bài viết này là từ Excel)Changed Type: Đây là bước mà power nguồn Query tự dấn kiểu định dạng cho mỗi trường thông tin (cột) khi nào Input dữ liệu vào. Nếu như khách hàng không mong muốn Power Query Editor auto thay thay đổi định dạng như vậy thì bạn có thể bỏ bước này bằng phương pháp ấn vào lốt X phía trước bước đó. Mình sẽ có tác dụng điều đó.Quay lại cùng với mục đích lúc đầu là đưa dữ liệu về dạng “Chuẩn”. Tại hành lang cửa số Power Query Editor bạn vào Tab Transform và xem xét tới mục Unpivot Columns (ta sẽ thực hiện nó xuyên suốt bài viết này).
Ta sẽ sở hữu 2 giải pháp làm như sau:
Cách 1: sử dụng Unpivot Columns hoặc Unpivot Only Selected Columns
Với biện pháp này, bạn chọn các cột Quý 1, Quý 2, Quý 3, Quý 4. Tiếp đến thực hiện Unpivot Columns hoặc Unpivot Only Selected Columns.
Ở đây mình lựa chọn Unpivot Columns. Sau đó bạn có thể đổi thương hiệu cột Attribute thành Quý. Power nguồn Query sẽ triển khai và ghi lại bước kia trong mục Applied Steps và chúng ta có tác dụng sau khi triển khai như sau:
Như vậy là dứt rồi, thật dễ dàng và đơn giản và hối hả phải không nào .
Giờ bạn chỉ việc quay lại Tab trang chủ chọn Close & Load vào Excel thôi là bạn đã sở hữu thể có được 1 bảng tài liệu đúng “Chuẩn” rồi. Ở đây, bản thân chọc Close và Load To…. Với chọn cách xem tài liệu trả về là Table và để ở ô I3 vào Sheet VD_1.
Kết trái trả về là 1 trong những Table với dữ liệu được biến hóa về đúng “Chuẩn” với những trường tin tức (cột) như hình dưới.
Cách 2: áp dụng Unpivot Other Columns
Với bí quyết này. Thay vì lựa lựa chọn 4 cột Quý 1, Quý 2, Quý 3, quý 4 như biện pháp 1 thì các bạn sẽ lựa chọn các cột sót lại trong bảng đó là cột STT cùng cột mặt hàng hoá. Vào Transform và chọn lọc Unpivot Other Columns
Bạn hoàn toàn có thể thấy, kết quả trả về cũng biến thành giống như giải pháp 1
Vậy, câu hỏi đặt ra là sự khác hoàn toàn giữa 2 giải pháp này là gì?
Sự khác biệt ở đây là: trường hợp bạn làm theo cách 1 thì giả sử khi bạn có thêm 1 hoặc nhiều cột nữa vào dữ liệu gốc (VD: thêm cột Quý 5, Quý 6,….) thì khi chúng ta cập nhật, power nguồn Query sẽ không còn tự hiểu để Unpivot cho đông đảo Columns quý mà chúng ta thêm new đó.
Còn với cách làm thứ hai lựa chọn Unpivot Other Columns đang khắc phục được điều đó. Mặc dù nhiên, bạn cũng cần phải hiểu rõ thực chất hơn nữa nhằm lựa chọn cách Unpivot đến phù hợp.
Ví dụ 2
Ở lấy một ví dụ này, bảng dữ liệu gốc lại được cấu tạo như sau:
Với tổ chức triển khai dữ liệu như này, thậm chí còn còn tồi tệ hơn ví dụ 1 bên trên . Ở đây đúng ra nên tổ chức thành những cột là STT, mặt hàng hoá, Tháng, lệch giá (DT), chi phí (CP) thì tất cả vẻ hợp lí hơn cần không nào. Vậy, làm điều đó như nỗ lực nào đây???. Copy, Paste tốt VBA đây???. Hãy quên những từ thời điểm cách đó đi vị đã tất cả Power Query.
Đầu tiền, bạn cần tạo 1 Table và đặt cho nó 1 cái tên (Mình sẽ đặt VD_2 là tên của Table này). Cũng với các bước như ví dụ như 1, ta triển khai Input dữ liệu vào power nguồn Query Editor. Vẫn với Unpivot, và vụ việc là ta đã Unpivot như nào để dữ liệu này ra được dạng “Chuẩn” ?.
Ta sẽ thực hiện điều ấy với quá trình sau:
Bước 1: triển khai Unpivot
Chọn Unpivot Other Columns, sau bước này ta được tác dụng như sau:
Bước 2: bóc cột Attribute thành 2 cột
Ta thấy, mọi ký tự bên buộc phải dấu _ nó chỉnh là tháng. Ta vẫn thực hiện tách bóc cột Attribute thành 2 cột, bằng anh tài Split Columns cùng với delimiter là vệt _
Trong trường hợp delimiter không gồm thì bạn cũng có thể tạo ra bằng phương pháp sửa thương hiệu cột mang lại phù hợp, để đáp ứng nhu cầu được cho vấn đề xử lý dữ liệu.
Xem thêm: Đàn Em Năm Cam Còn Sống Hay Chết, Tôi Là Người Sài Gòn
Kết quả sau khi thực hiện bước này bạn sẽ được như sau, thời gian cột Attribute.2 nó đó là cột Tháng cơ mà ta cần.
Bước 3: Pivot cột Attribute.1
Cột Attribute.1 đang chứa cả DT (doanh thu) với CP (chi phí), lẽ ra thì nó bắt buộc là 2 cột nên không, 1 cột là DT cùng 1 cột là CP.
Bạn chọn cột Attribute.1 cùng cột Value để triển khai Pivot. Tác dụng sau bước này bạn sẽ có bảng tài liệu đúng “Chuẩn” như hình
Nhìn dữ liệu sau khi biến hóa có vẻ một cách khách quan và hợp lí hơn ban sơ phải không nào. Hiện thời bạn chỉ cần thực hiện một trong những thao tác đơn giản dễ dàng như thay tên tiêu đề những cột, format dữ liệu cho những cột là có thể Close và Load vào Excel được rồi.
Có ngôi trường hợp dữ liệu nào khó khăn đỡ hơn nữa không đây???. Hãy cùng Học Excel Online sang lấy một ví dụ 3.
Ví dụ 3
Cũng với dữ liệu hệt như các lấy một ví dụ trước, mà lại lần này tiêu đề của chúng ta lại bao gồm Merger (một điều mà bạn nên hạn chế sử dụng).
Với trường phù hợp tiêu đề của bảng tất cả Merger thì ta có một vài hướng xử trí như sau:
Cách 1: cách xử trí tiêu đề trên Excel luôn
Với cách này, bạn sẽ bỏ Merger ngay vào Excel luôn và thay tên lại các tiêu đề đó phù hợp, để có thể đưa về được dạng như lấy ví dụ 2 nhưng mà Học Excel Online đã ra mắt ở trên.
Cách 2: Xử lý bên trong Power Query Editor
Tất nhiên là ta cũng cần phải chuyển dữ liệu này về dạng như lấy ví dụ như 2. Cách xử lý như nào trên đây trong power nguồn Query???
Đầu tiên thì cũng như các lấy ví dụ trước là bạn làm cho nó 1 Table và đặt thương hiệu (mình vẫn đặt tên ở đây là VD_3).
Lưu ý: Trường phù hợp này ta sẽ quăng quật chọn phần “My table has headers” trong hộp thoại chế tạo Table.
Thực hiện nay Input dữ liệu vào nguồn Query với ta cũng trở nên cần một số bước để chuyển đổi dữ liệu này về dạng “Chuẩn”, cụ thể như sau:
Sau khi Input tài liệu vào Power Query ta sẽ trông nó như này đây, phần đa ô không tồn tại dữ liệu gì vẫn hiện null.
Bước 1: thực hiện đổi tên cho các cột (có quy tắc độc nhất vô nhị định)
Ở cách này, ta vẫn đổi tên cho những cột này theo một quy tắc nhất mực để rất có thể vận dụng được công việc trong lấy một ví dụ 2, cụ thể như sau:
Bước 2: thực hiện xoá dòng
Tiếp theo ta đã xoá 2 dòng trước tiên trong dữ liệu bằng chức năng Remove Rows trong Tab Home.
Bước 2: Thực hiện các bước như ví dụ 2
Sau khi tiến hành 2 cách trên, dữ liệu lúc này sẽ hệt như dữ liệu cội ở ví dụ như 2, và các bạn sẽ thực hiện các bước tương tự để trả về được dữ liệu đúng “Chuẩn”. Chúng ta Unpivot -> tách cột -> Pivot, nhằm ra được kết quả giống tác dụng của ví dụ như 2.
Một vài lưu giữ ý
Qua 3 lấy ví dụ xử lý dữ liệu trên, ta bao gồm một vài xem xét như sau:
– Các làm việc ta thực hiện trong power Query Editor sẽ tiến hành M Code lưu lại (M Code là ngôn ngữ mà power nguồn Query thực hiện để ghi lại các bước được áp dụng) tương tự như Code VBA trong Excel. Điều này hết sức thuận khi chúng ta cập nhật thêm, bớt, chỉnh sửa,…. Tài liệu ở DATA cội thì bạn chỉ việc Refresh. Ngay mau chóng những quy trình bạn thực hiện trong nguồn Query Editor sẽ xúc tiến và cập nhật kết quả mang đến bạn 1 cách nhanh chóng, thiết yếu xác.
– Trong nội dung bài viết này, ở cách 1 và bước 2 trong Vi dụ 3 này vừa rồi ta buộc phải làm thủ công, điều này hoàn toàn có thể sẽ có một số vấn đề như khi bạn thêm các ngôi trường (cột) dữ liệu khác vào DATA cội thì việc đổi tên cột đó Power Query Editor sẽ thiếu hiểu biết nhiều để đổi cho mình thành thương hiệu gì. Bởi vì đó, giỏi hơn không còn là bạn nên tổ chức dữ liệu một cách hiệu trái hơn.
– từ đây bạn có thể rút ra: Các làm việc mà ta triển khai trong Power Query Editor cần phải có tính chung, thống nhất, ta phải tạo nên 1 quy trình chung vào xử lý tài liệu để mỗi lần update dữ liệu nơi bắt đầu thì các bước này vẫn vận động đúng và trả về kết quả đúng đắn cho chúng ta.
Kết luận
Học Excel Online hy vọng sắp tới bạn đã rứa được biện pháp xử lý dữ liệu, chuyển đổi dữ liệu về dạng “Chuẩn” với nguồn Query vào Excel. Power Query là 1 trong công cụ mạnh dạn được Excel cung cấp giúp cho mình xử lý dữ liệu, biến đổi dữ liệu một cách khá đối kháng giản, tác dụng và hiệu suất cao. Với Power Query hiện nay việc chuẩn hoá dữ liệu, gộp file, gộp các Sheets trong Excel trở nên đơn giản dễ dàng hơn khi nào hết
Power Query là 1 công nuốm rất mạnh. Tuy nhiên, còn khá mớ lạ và độc đáo với chúng ta. Câu hỏi nắm được cách sử dụng công cố kỉnh này vẫn là mong nối giữa Excel tới power BI – Một công cụ để gia công báo cáo tuyệt vời.
