Lý Do Sử Dụng Microsoft Power Query Cho Excel

--- Bài mới hơn ---

  • Tổng Quan Power Query: Giới Thiệu Về Công Cụ Dữ Liệu Mạnh Nhất Của Excel
  • Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query
  • Hướng Dẫn Sử Dụng Power Query Để Chuẩn Hoá Dữ Liệu
  • Cách Sử Dụng Hiệu Quả Find Và Replace Nâng Cao Trong Excel
  • Hướng Dẫn Toàn Tập Excel 2022 (Phần 10): Sử Dụng Chức Năng Find Và Replace
  • Microsoft Excel là phần mềm bảng tính mạnh mẽ và phổ biến giúp làm việc với dữ liệu đơn giản. Microsoft đã phát triển một số cách khá sáng tạo để làm việc với dữ liệu.

    Nếu đang tìm kiếm một cách mới mạnh mẽ để quản lý dữ liệu, Microsoft Excel cho Windows hiện có phần mềm được tích hợp để trợ giúp đó là Microsoft Power Query. Đây là một công cụ mới giúp bạn kiểm soát bảng tính của mình nhiều hơn bao giờ hết.

    Microsoft Power Query là gì?

    Power Query (được biết đến trong Microsoft Excel 2022 với tên gọi Get & Transform) là một công cụ chuyển đổi dữ liệu được tích hợp ngay trong Microsoft Excel. Nó cho phép nhập dữ liệu từ nhiều nguồn khác nhau, thực hiện thay đổi và tải dữ liệu ngay vào workbook Excel.

    Khi nhập vào Power Query, nó sẽ tạo kết nối đến dữ liệu. Sử dụng kết nối này, bạn có thể làm việc với dữ liệu trong trình chỉnh sửa và thực hiện các thay đổi trước khi lưu.

    Power Query sẽ lưu thay đổi dưới dạng các bước trong một file được gọi là Query để có thể sử dụng bất cứ khi nào. Bạn có thể nghĩ nó như macro trong lập trình VBA.

    Nếu thường xuyên làm việc với dữ liệu, đây là lý do tuyệt vời để sử dụng Power Query.

    1. Microsoft Power Query được tích hợp trong Excel

    Power Query rất đơn giản. Nếu sử dụng Excel 2022 trở lên trên máy tính, người dùng đã có Power Query. Bạn không cần phải tải bất cứ phần mềm bổ sung hoặc có kiến thức lập trình để sử dụng công cụ này.

    Lưu ý: Đối với lập trình viên, Microsoft phát triển một ngôn ngữ mới cho Power Query được gọi là M. Người dùng nâng cao có thể viết code script sử dụng ngôn ngữ M. Đây là một khái niệm cao cấp hơn nhưng cung cấp thêm sức mạnh và giá trị cho người dùng chuyên nghiệp.

    2. Dễ sử dụng trình chỉnh sửa

    Power Query cung cấp giao diện đơn giản để làm việc với dữ liệu. Khi tải dữ liệu bạn có thể thực hiện thay đổi bên trong trình chỉnh sửa, không yêu cầu lập trình.

    Nếu đã quen thuộc với Excel, việc sử dụng trình chỉnh sửa dễ dàng hơn nhiều vì nó trông rất giống với cửa sổ trong Excel.

    Nếu cần chỉnh sửa dữ liệu, bạn có thể thực hiện ngay trong trình chỉnh sửa. Một số ví dụ bạn có thể thực hiện với dữ liệu:

    • Thêm hoặc xóa cột trong bảng
    • Lọc dữ liệu theo cột
    • Kết hợp hai bảng với nhau
    • Sắp xếp dữ liệu bằng cách sử dụng bộ lọc Excel
    • Đổi tên cột
    • Xóa lỗi từ bảng

    Khi thực hiện xong thay đổi, chỉ cần xuất kết quả ngay vào workbook Excel. Thật dễ dàng để làm việc với nhiều trang tính mà không cần mở từng trang và thực hiện thay đổi.

    3. Thực hiện các công việc lặp lại một cách đơn giản

    Một việc phổ biến khi làm việc với workbook Excel là phải thực hiện cùng một số thay đổi cho các bảng tính khác nhau.

    Giả sử bạn có báo cáo bán hàng tháng 7 và cần thực hiện một số thay đổi. Mở bảng tính, thay đổi và lưu. Sau một tháng, bạn lại có báo cáo tháng 8 và cũng cần thực hiện thay đổi tương tự.

    Với Excel, người dùng cần mở workbook và thực hiện tất cả các thay đổi để có kết quả tương tự. Tùy thuộc vào số lượng thay đổi, quá trình này có thể mất nhiều thời gian của bạn. Tuy nhiên Power Query có thể giải quyết vấn đề này.

    Khi kết nối workbook sử dụng Power Query và thực hiện thay đổi, những thay đổi này được lưu thành các bước. Các bước chỉ là hướng dẫn bạn cung cấp cho Excel để thay đổi dữ liệu (ví dụ, xóa cột này hoặc sắp xếp bảng này theo màu sắc).

    4. Không cần kiến thức lập trình

    Excel có thể lập trình bằng Visual Basic for Application (VBA), nhưng có nhiều thứ cần phải học. Bạn cũng có thể sử dụng các ngôn ngữ lập trình khác với Excel nhưng đòi hỏi nhiều thiết lập.

    5. Thu thập dữ liệu từ các nguồn khác nhau

    Power Query có thể lấy dữ liệu từ nhiều nơi khác nhau. Nếu muốn làm việc với dữ liệu từ trang web, bạn có thể nhập nó dễ dàng. Power Query cũng có thể lấy dữ liệu từ nhiều nguồn khác nhau, chẳng hạn như:

    Có nhiều nguồn khác bạn có thể sử dụng, nhưng đừng làm dụng.

    Tại sao không chỉ sử dụng Microsoft Excel?

    Excel khá mạnh mẽ, không có nghi ngờ gì về nó. Nhưng nếu sử dụng nhiều trang tính khác nhau, bạn sẽ có nhiều công việc cần quản lý.

    Power Query không phải là bảng tính mà là bảng điều khiển để bạn có thể làm việc với dữ liệu. Bố cục tương tự như Excel để giúp bạn làm việc dễ dàng hơn. Bạn có thể làm việc rất hiệu quả bằng cách kết hợp các điểm mạnh của Excel và Power Query.

    Có rất nhiều lý do tuyệt vời để bắt đầu sử dụng Power Query quản lý bảng tính. Nếu nắm vững những điều cơ bản của chương trình này, bạn có thể tạo các bảng và làm việc với dữ liệu dễ dàng hơn nhiều.

    --- Bài cũ hơn ---

  • Enabling Power Pivot Excel 2022
  • Nâng Cấp Mô Hình Dữ Liệu Power Pivot Cho Excel 2013 Hoặc Excel 2022
  • Tạo Pivottable Để Phân Tích Dữ Liệu Trang Tính
  • Sử Dụng Công Cụ Pivottable Và Pivotchart Phân Tích Dữ Liệu Trên Excel
  • Cách Dùng Hàm Rank Để Sắp Xếp Thứ Hạng Trong Excel
  • Hướng Dẫn Sử Dụng Name Động Với Power Query Trong Excel

    --- Bài mới hơn ---

  • Dao, Ado,query Table Với Việc Trích Rút Dữ Liệu Trong Excel.
  • Cách Tạo Một Câu Hỏi Truy Vấn (Query) Căn Bản Trong Access 2022
  • Hướng Dẫn Tạo Báo Cáo Động Query Table Trong Excel Và Add
  • Lỗ Hổng Trong Excel Cho Phép Hacker Tấn Công Malware Thông Qua Tính Năng Power Query
  • Hướng Dẫn Sử Dụng Các Hàm Tìm Kiếm Trên Excel 2007, 2010, 2013, 2022
  • Ta có thể sử dụng được Name động trong Excel, vậy còn trong Power Query thì sao? Trong bài viết này Học Excel Online sẽ hướng dẫn cách để sử dụng được Name động trong Power Query.

    Tạo Name động

    Để tạo Name động trong Excel ta có một số hàm thường dùng như OFFSET, nếu bạn đang dùng các phiên bản Excel mới hơn như Excel 2022, 365 thì sẽ có khá nhiều cách nữa để tạo được Name động.

    Để cho các phiên bản Excel khác đều có thể thực hiện thì trong bài viết này mình sẽ sử dụng hàm OFFSET để tạo name động.

    Cụ thể, mình có 1 bảng dữ liệu như sau, ta sẽ dùng hàm OFFSET để tạo 1 Name động và Name này chính là toàn bộ dữ liệu của chúng ta

    Hướng dẫn cách tạo danh sách động trong excel với hàm OFFSET

    Bạn vào Define Name, tại hộp thoại New Name bạn làm như sau:

    • Refers to: Bạn gõ vào công thức tạo Name động với hàm OFFSET như sau:

    Sau khi đã tạo được Name động với hàm OFFSET như trên, bạn vào Power Query, trong Get Data bạn chọn Launch Power Query Editor…. Lúc này cửa sổ tạo Power Query Editor sẽ hiện ra.

    Bạn tạo 1 Blank Query , bạn có thể làm điều điều này trong Tab Home, nhóm New Query hoặc bạn chuột phải vào phần bên trái của cửa sổ Query Editor, cụ thể như sau:

    = Excel.CurrentWorkbook(){

    Nếu như chưa hiểu về M – Code thì bạn chỉ cần quan tâm tới phần {[Name= “DATA”]}

    Bạn gõ tên Name động của bạn vào phần này sau đó ấn Enter bạn sẽ có kết quả như sau:

    Một vài lưu ý

    Bạn có thể thấy chúng ta hoàn toàn có thể sử dụng được Name động trong Power Query. Tuy nhiên. Học Excel Online nghĩ bạn không nên quá lạm dụng việc này trong trường hợp không cần thiết, thay vào đó bạn nên tạo Table cho dữ liệu để sử dụng Power Query. Định dạng Table được khuyến khích sử dụng và cho hiệu suất tốt nhất so với Name Range thông thường và Name động này.

    Hy vọng qua bài viết này của Học Excel Online bạn đã nắm được cách để có thể sử dụng được Name động trong Power Query.

    Power Query là một công cụ rất mạnh được Microsoft hỗ trợ cho khá nhiều phiên bản Excel được sử dụng phổ biến hiện nay, việc nắm được Power Query trong Excel nó sẽ là cầu nối giúp cho bạn tiến tới 1 công cụ rất mạnh trong việc làm các báo cáo phân tích – Power BI

    Hướng dẫn cách tạo danh sách động trong excel với hàm OFFSET Hàm OFFSET và các kết hợp hàm của nó trong excel, công thức ví dụ Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query Hàm DAX trong Power BI PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay

    --- Bài cũ hơn ---

  • Cách Lấy Dữ Liệu Từ Bảng Này Sang Bảng Khác Trong Excel
  • Cách Khai Báo Và Sử Dụng Biến Dữ Liệu Trong Vba Excel
  • Hướng Dẫn Cách Lập Công Thức Tự Động Điền Hệ Số Lương Theo Ngạch Bậc Trong Bảng Lương Excel
  • Cách Lấy Giá Trị Nhập Cuối Cùng Của Hàng, Cột Excel
  • Hướng Dẫn Sao Chép Công Thức Trong Excel Mà Không Làm Thay Đổi Dữ Liệu Tham Chiếu
  • Hướng Dẫn Sử Dụng Power Query Để Chuẩn Hoá Dữ Liệu

    --- Bài mới hơn ---

  • Cách Sử Dụng Hiệu Quả Find Và Replace Nâng Cao Trong Excel
  • Hướng Dẫn Toàn Tập Excel 2022 (Phần 10): Sử Dụng Chức Năng Find Và Replace
  • Cách Đánh Số Thứ Tự Trong Excel Khi Lọc Dữ Liệu
  • 3 Cách Đánh Số Thứ Tự Tự Động Trong Excel
  • Cách Đánh Số Thứ Tự Trong Excel Nhanh
  • Trong bài viết này, Học Excel Online sẽ hướng dẫn cách chuẩn hoá dữ liệu, nói cách khác là đưa dữ liệu về dạng “Chuẩn” với công cụ Power Query trong Excel.

    Chuẩn bị dữ liệu tốt có lẽ là yếu tố thành công quan trọng nhất trong phân tích dữ liệu. Nếu bố cục dữ liệu không đúng, bạn sẽ tiếp tục chiến đấu với Excel; phải sử dụng các công thức Excel nâng cao, rất 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,….

    Ở đây, mình có bảng dữ liệu như sau:

    Vậy làm như thế nào để ta có thể thiết kế lại dữ liệu như đã mô tả trên?

    Bạn sẽ làm điều đó trong Excel như thế nào?. Bạn sẽ Copy và Paste một cách thủ công và rất dễ sai sót, nhầm lẫn,…và mỗi lần dữ liệu gốc thay đổi bạn lại Copy lại???. Hay là bạn sẽ xử lý nó với kỹ năng VBA mà mình có???

    KHÔNG, Không nên một chút nào nếu như bạn đã có công cụ Power Query thì hãy để nó xử lý việc đó cho bạn một cách 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à đặt tên cho Table nó 1 cái tên (ở đây mình đã đặt tên là VD_1). Power Query hỗ trợ cho bạn khá nhiều kiểu để Input dữ liệu vào như Table, Name range (Define name), Name động hay thậm chí bạn chỉ cần quét chọn 1 vùng thì Power Query sẽ nhận vùng đó để đưa vào Power Query Editor. Tuy nhiên, với dữ liệu được lấy từ Excel thì tốt nhất là bạn nên tạo Table cho nó.

    Tiếp theo, bạn chỉ cần chọn 1 ô bất kỳ trong bảng đó vào chọn From Table/Range

    • Source: Đây là nguồn dữ liệu mà 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 Query tự nhậ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 bạn không muốn Power Query Editor tự động thay đổi định dạng như vậy thì bạn có thể bỏ bước này bằng cách ấn vào dấu X phía trước bước đó. Mình sẽ làm điều đó.

    Quay lại với mục đích ban đầu là đưa dữ liệu về dạng “Chuẩn”. Tại cửa sổ Power Query Editor bạn vào Tab Transform và để ý tới mục Unpivot Columns (ta sẽ sử dụng nó xuyên suốt bài viết này).

    Cách 1: Sử dụng Unpivot Columns hoặc Unpivot Only Selected Columns

    Với cách này, bạn chọn các cột Quý 1, Quý 2, Quý 3, Quý 4. Sau đó thực hiện Unpivot Columns hoặc Unpivot Only Selected Columns.

    Giờ bạn chỉ cần quay lại Tab Home chọn Close & Load vào Excel thôi là bạn đã có thể có được 1 bảng dữ liệu đúng “Chuẩn” rồi. Ở đây, mình chọc Close & Load To…. và chọn cách xem dữ liệu trả về là Table và đặt tại ô I3 trong Sheet VD_1.

    Với cách này. Thay vì lựa chọn 4 cột Quý 1, Quý 2, Quý 3, Quý 4 như cách 1 thì bạn sẽ lựa chọn các cột còn lại trong bảng đó là cột STT và cột Hàng hoá. Vào Transform và lựa chọn Unpivot Other Columns

    Sự khác biệt ở đây là: Nếu 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 bạn cập nhật, Power Query sẽ không tự hiểu để Unpivot cho những Columns quý mà bạn thêm mới đó.

    Còn với cách làm thứ 2 lựa chọn Unpivot Other Columns sẽ khắc phục được điều đó. Tuy nhiên, bạn cũng cần hiểu rõ bản chất hơn nữa để lựa chọn cách Unpivot cho phù hợp.

    Ở ví dụ này, bảng dữ liệu gốc lại được cấu trúc như sau:

    Đầu tiền, bạn cần tạo 1 Table và đặt cho nó 1 cái tên (Mình đã đặt VD_2 là tên của Table này). Cũng với các bước như ví dụ 1, ta thực hiện Input dữ liệu vào Power Query Editor. Vẫn với Unpivot, và vấn đề là ta sẽ Unpivot như nào để dữ liệu này ra được dạng “Chuẩn” ?.

    Bước 1: Thực hiện Unpivot

    Chọn Unpivot Other Columns, sau bước này ta được kết quả như sau:

    Ta thấy, những ký tự bên phải dấu _ nó chỉnh là tháng. Ta sẽ thực hiện tách cột Attribute thành 2 cột, bằng tính năng Split Columns với delimiter là dấu _

    Trong trường hợp delimiter không có thì bạn có thể tạo ra bằng cách sửa tên cột cho phù hợp, để đáp ứng được cho việc xử lý dữ liệu.

    Kết quả sau khi thực hiện bước này bạn sẽ được như sau, lúc cột Attribute.2 nó chính là cột Tháng mà ta cần.

    Cột Attribute.1 đang chứa cả DT (doanh thu) và CP (chi phí), đúng ra thì nó phải là 2 cột phải không, 1 cột là DT và 1 cột là CP.

    Bạn chọn cột Attribute.1 và cột Value để thực hiện Pivot. Kết quả sau bước này bạn sẽ có bảng dữ liệu đúng “Chuẩn” như hình

    Cũng với dữ liệu giống như các ví dụ trước, nhưng lần này tiêu đề của bạn lại có Merger (một điều mà bạn nên hạn chế sử dụng).

    Với trường hợp tiêu đề của bảng có Merger thì ta có một số hướng xử lý như sau:

    Cách 1: Xử lý tiêu đề trên Excel luôn

    Với cách này, bạn sẽ bỏ Merger ngay trong Excel luôn và đổi tên lại các tiêu đề đó phù hợp, để có thể đưa về được dạng như Ví dụ 2 mà Học Excel Online đã giới thiệu ở trên.

    Cách 2: Xử lý bên trong Power Query Editor

    Đầu tiên thì cũng như các Ví dụ trước là bạn tạo cho nó 1 Table và đặt tên (mình đã đặt tên ở đây là VD_3).

    Lưu ý: Trường hợp này ta sẽ bỏ chọn phần “My table has headers” trong hộp thoại tạo Table.

    Thực hiện Input dữ liệu vào Power Query và ta cũng sẽ cần một số bước để biến đổi dữ liệu này về dạng “Chuẩn”, cụ thể như sau:

    Sau khi Input dữ liệu vào Power Query ta sẽ trông nó như này đây, những ô không có dữ liệu gì sẽ hiện n ull.

    Ở bước này, ta sẽ đổi tên cho các cột này theo một quy tắc nhất định để có thể vận dụng được các bước trong Ví dụ 2, cụ thể như sau:

    Bước 2: Thực hiện xoá dòng

    Tiếp theo ta sẽ xoá 2 dòng đầu tiên trong dữ liệu bằng chức năng Remove Rows trong Tab Home.

    Qua 3 ví dụ xử lý dữ liệu trên, ta có một vài lưu ý như sau:

    – Các thao tác ta thực hiện trong Power Query Editor sẽ được M Code ghi lại (M Code là ngôn ngữ mà Power Query sử dụng để ghi lại các bước được áp dụng) gần giống như Code VBA trong Excel. Điều này rất thuận khi chúng ta cập nhật thêm, bớt, chỉnh sửa,…. dữ liệu ở DATA gốc thì bạn chỉ cần Refresh. Ngay lập tức những quy trình bạn thực hiện trong Power Query Editor sẽ thực thi và cập nhật kết quả cho bạn 1 cách nhanh chóng, chính xác.

    – Trong bài viết này, ở Bước 1 và Bước 2 trong Vi dụ 3 này vừa rồi ta phải làm thủ công, điều này có thể sẽ có một số vấn đề như khi bạn thêm các trường (cột) dữ liệu khác vào DATA gốc thì việc đổi tên cột đó Power Query Editor sẽ không hiểu để đổi cho bạn thành tên gì. Do đó, tốt hơn hết là bạn nên tổ chức dữ liệu 1 cách hiệu quả hơn.

    – Từ đây bạn có thể rút ra: Các thao tác mà ta thực hiện trong Power Query Editor cần có tính chung, thống nhất, ta phải tạo ra 1 quy trình chung trong xử lý dữ liệu để mỗi lần cập nhật dữ liệu gốc thì quy trình này vẫn hoạt động đúng và trả về kết quả chính xác cho chúng ta.

    Học Excel Online hy vọng tới đây bạn đã nắm được cách xử lý dữ liệu, chuyển đổi dữ liệu về dạng “Chuẩn” với Power Query trong Excel. Power Query là một công cụ mạnh được Excel hỗ trợ giúp cho bạn xử lý dữ liệu, biến đổi dữ liệu một cách khá đơn giản, hiệu quả và hiệu suất cao. Với Power Query bây giờ việc chuẩn hoá dữ liệu, gộp file, gộp các Sheets trong Excel trở nên đơn giản hơn bao giờ hết

    Power Query là một công cụ rất mạnh. Tuy nhiên, còn khá mới mẻ với chúng ta. Việc nắm được cách sử dụng công cụ này sẽ là cầu nối giữa Excel tới Power BI – Một công cụ để làm báo cáo tuyệt vời.

    Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay

    --- Bài cũ hơn ---

  • Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query
  • Tổng Quan Power Query: Giới Thiệu Về Công Cụ Dữ Liệu Mạnh Nhất Của Excel
  • Lý Do Sử Dụng Microsoft Power Query Cho Excel
  • Enabling Power Pivot Excel 2022
  • Nâng Cấp Mô Hình Dữ Liệu Power Pivot Cho Excel 2013 Hoặc Excel 2022
  • Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot

    --- Bài mới hơn ---

  • Hướng Dẫn Về Chức Năng Power Query Trong Power Pivot
  • How To Merge Tables With Power Query
  • How To Combine Tables With Power Query
  • Conditional Merge In Power Query
  • Cách Tính Tiền Thưởng Và Hoa Hồng Đơn Giản Với Vlookup () Trong Excel
  • Cách làm như sau:

    Bước 2: Duyệt tới thư mục nơi chứa file Access và chọn file

    Bước 3: Đánh dấu vào mục Select Multiple Items (1), sau đó chọn các bảng dữ liệu (2) rồi bấm Load (3)

    Sau khi Load dữ liệu xong, chúng ta sẽ có kết quả là cửa sổ Queries & Connections xuất hiện phía bên phải màn hình làm việc của Excel, đồng thời trong mục Queries sẽ bao gồm 5 Queries

    Lưu ý:

    Có thể bạn đang có câu hỏi: Tại sao chúng ta cần nạp dữ liệu thông qua Power Query mà không nạp trực tiếp vào Power Pivot?

    Câu trả lời là: Bởi vì không phải bảng dữ liệu nào (hay bảng tham chiếu nào) cũng đúng tiêu chuẩn làm việc của Power Pivot. Có những bảng có nhiều hơn 1 dòng tiêu đề, có những bảng mà có gộp ô, trộn ô tại dòng tiêu đề, hay có dòng trống trong bảng… Rất ít trường hợp bảng dữ liệu đúng tiêu chuẩn ngay và có thể dùng trực tiếp được. Thường bạn chỉ gặp khi học, khi đọc sách, xem tài liệu mẫu, còn trong thực tế thì không được như vậy. Do đó để đảm bảo bạn có thể ứng dụng kiến thức trong sách này vào thực tế, chúng ta cần biết cách sử dụng Power Query thay vì chỉ dùng trực tiếp trong Power Pivot.

    Hướng dẫn về Power Query trong Power Pivot:

    https://blog.gitiho.com/wp-admin/post.php?post=45404&action=edit

    Hướng dẫn sử dụng Power Query để sửa đổi bảng dữ liệu trong Power Pivot:

    https://blog.gitiho.com/wp-admin/post.php?post=45432&action=edit

    Thời đại công nghệ 4.0 đang dần đi vào cuộc sống đòi hỏi mọi người phải tự trang bị kiến thức tin học cho phù hợp để có thể bắt kịp nhưng thay đổi nhanh chóng này. Chẳng ai khác ngoài bạn hiểu mình cần trang bị thêm kiến thức gì. Hãy tìm hiểu ngay TẠI ĐÂY những khóa học hấp dẫn, có tính thực tế cao của Gitiho và đăng ký nhận tư vấn ngay hôm nay.

    Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

    Gitiho tự hào khi được đồng hành cùng:

      50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

    --- Bài cũ hơn ---

  • How To Create A Microsoft Query In Excel (Excel Query)
  • Power Query Overview: An Introduction To Excel’s Most Powerful Data Tool
  • The Complete Guide To Power Query
  • Hướng Dẫn Cách Dùng Hàm Pi Trong Power Bi Dax
  • Refresh Pivot Tables Automatically When Source Data Changes
  • Power Query Overview: An Introduction To Excel’s Most Powerful Data Tool

    --- Bài mới hơn ---

  • How To Create A Microsoft Query In Excel (Excel Query)
  • Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot
  • Hướng Dẫn Về Chức Năng Power Query Trong Power Pivot
  • How To Merge Tables With Power Query
  • How To Combine Tables With Power Query
  • Bottom line: Learn how this awesome feature of Excel and Power BI called Power Query will help you automate the process of importing, transforming, and cleansing your data to save a TON of time with your job.

    Skill level: Beginner

    Video Tutorial

    Download the Sample Files

    The CSV files I use in the video are available for download below. You will need to extract the files out of the zip file.

    Introduction to Power Query

    In this tutorial I provide an introductory explanation of Power Query. You will learn why this is my new favorite Excel tool for working with data, and how it can help automate processes and save you time!

    The Power Query Data Machine

    I was watching a TV show on how things are made, and they were explaining how a depositor machine worked in a pastry factory.

    The basics of a depositor machine are:

    1. You add ingredients to it.
    2. Change some settings.
    3. And it magically creates pastries (cookies, donuts, biscuits) that are ready for baking.

    Once the dials are set, the process can be repeated over-and-over again to make perfect pastries every time. Getting hungry…? 🙂

  • You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
  • Press buttons in the Power Query Editor window to transform your data.
  • Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.
  • If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding.

    Common Data Tasks Made Easy

    Do you work with data that has been exported from a system of record? This could be a general ledger, accounting, ERP, CRM, chúng tôi or any reporting system that contains data.

    If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.

    These data transformations could include tasks like:

    • Remove columns, rows, blanks
    • Convert data types – text, numbers, dates
    • Split or merge columns
    • Sort & filter columns
    • Add calculated columns
    • Aggregate or summarize data
    • Find & replace text
    • Unpivot data to use for pivot tables

    Do any of these tasks sound familiar? If so, then they probably also sound boring, repetitive, and time consuming. 🙂 Believe me, I’ve spent the better part of my career doing these tasks and trying to p out faster ways to get them done.

    Fortunately, Power Query has buttons that automate all these tasks!

    Overview of the Power Query Ribbon

    Starting in Excel 2022 for Windows, Power Query has been fully integrated into Excel. It is now on the Data tab of the Ribbon in the Get & Transform group.

    In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will be visible in the Excel Ribbon.

    You use the buttons in the Data or Power Query tab to get your source data. Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, chúng tôi Dynamics CRM, Facebook, Wikipedia, websites, and more.

    Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.

    The buttons in the Power Query Editor Window allow you to transform your data.

    Think about some of those tasks you do repeatedly as you browse the buttons in the image above. Each time you pss a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data by refreshing the query.

    You can also modify existing queries and refresh your output tables with the changes or updated data.

    Data Transformation Examples

    Here are a few examples of what Power Query can do with your data.

    Unpivot Data for Pivot Tables

    My favorite feature of Power Query is it’s ability to Unpivot data. This is a technique used to get your data ready for the source of a pivot table. This is also referred to as normalizing your data to get it in a tabular format.

    The data might start out looking something like the following.

    And you want the end result to look like this.

    Here is an article and video on exactly How to Unpivot Your Data with Power Query.

    Checkout my article on how to structure your source data for a pivot table if you are unfamiliar with why your data needs to look like this for a pivot table.

    Append (Combine) Tables with Power Query

    The Append feature of Power Query allows you to combine multiple tables (stack them vertically) to create one large table. It can do this with multiple tables in one file, or it can pull in data from a bunch of different files/sources.

    Let’s say you have a folder that contains CSV or Excel files with report data for each month. Throw all those files in the Power Query machine, and it will spit out one nice table that you can then use to create pivot tables and charts.

    If the data in those reports also needs to be transformed (remove rows, split columns, unpivot, etc.), then Power Query can handle that in the same process.

    Once it is setup, all you have to do is hit the refresh button every month when a new file is added to the folder and the rows will be added to your output table.

    How awesome is that! 🙂

    Merge Tables – A VLOOKUP Alternative

    Power Query has the ability to merge or join tables. This can be used as an alternative to VLOOKUP or INDEX/MATCH formulas.

    Let’s say you have this data table of sales records, and you are using a VLOOKUP to bring in information about the product based on the name of the product sold. Your product group information is located in another table on a different sheet or workbook.

    Using VLOOKUP formulas is great, but it can often mean adding thousands of formulas to your workbook. Which increases the file size and calculation time.

    Create Custom Functions

    However, Power Query can be programmed to create custom functions. This gives you seemingly unlimited potential to transform your data in just about any way possible.

    It is based on the M language, and most of the functions are very similar to writing a formula in Excel. This also makes it more user friendly and easier to learn the code.

    This new language and set of functions means there is a lot to learn, but I consider that the fun and challenging part. Plus, employers of the future will definitely be looking for employees with Power Query skills.

    Power Query Records Your Steps & Automates Processes

    Power Query not only makes all these tasks easier, but it also records your steps so you do NOT have to do them over-and-over again. It will save you a lot of time if you are pparing the same data every day, week, or month.

    It also does a ptty good job of handling errors. If the structure of your source data changes, Power Query will tell you what step it broke at and allow you to fix it. This makes maintenance easy and you don’t have to completely redo your process when something changes.

    You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports. This is a critical step in the process of summarizing and analyzing data.

    The Power Query Machine & Power BI

    Well, it can’t exactly make cookies, but Power Query is a ptty awesome tool! It will save you a ton of time when transforming your data.

    Power Query is just one piece of the suite of Power BI (Business Intelligence) products from Microsoft.

    If we go back to the analogy of baking cookies in a factory, you can think of Power Query as the first step in the assembly line. Once the cookies are formed, we then need to bake them (Pivot Tables, PowerPivot) and then package them for psentation (Power View, Power Map, Charts, Dashboards, etc.)

    How Do I Get Power Query?

    The other nice part is that Power Query is now built into Excel starting with Excel 2022 for Windows. If you are on Excel 2010 or 2013 then Power Query is a free add-in.

    I have a dedicated page that will help you determine if you have the right version of Excel to get Power Query. It also provides complete installation instructions and the download link.

    Complete Guide to Installing Power Query

    To give you an idea of the importance of this tool, Power Query was fully integrated into Excel in Excel 2022 for Windows, and is on the Data tab of the Ribbon.

    It is also known as Get & Transform, although the term Power Query is most common.

    Additional Resources

    This article has provided an overview of the basics of Power Query that should help you understand some of the major features. Power Query has a ton of features and there is definitely a lot to learn.

    I will be sharing more how-to articles and videos in the coming weeks. Here are a few resources that will help you get started.

    How to Unpivot Your Data with Power Query + Video Tutorial

    Free Training Webinar on the Power Tools

    Right now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.

    It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.

    You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.

    If not, do you think it would be useful for you? Are you doing any of the tasks I mentioned manually right now?

    I will be creating more how-to articles and videos on Power Query in the future, so I’m interested to know what you want to learn.

    Thank you! 🙂

    --- Bài cũ hơn ---

  • The Complete Guide To Power Query
  • Hướng Dẫn Cách Dùng Hàm Pi Trong Power Bi Dax
  • Refresh Pivot Tables Automatically When Source Data Changes
  • How To Refresh Pivot Table When Data Changes In Excel?
  • How To Refresh Pivot Table In Excel (Manually + Auto
  • Tạo Các Công Thức Power Query Trong Excel

    --- Bài mới hơn ---

  • Cách Áp Dụng Replace Trong Excel Hiệu Quả
  • Hướng Dẫn 3 Cách Thay Đổi Chữ Thường Thành Chữ Hoa Và Ngược Lại Trong Excel 2022, 2013, 2010
  • Một Số Phím Tắt Thông Dụng Nhất Trong Excel Không Thể Bỏ Qua
  • Hàm Frequency Trong Excel, Hàm Tính Giá Trị Thường Xuyên Xuất Hiện
  • Hướng Dẫn Cách Lồng Ghép Hàm, Viết Hàm Lồng Nhau Trong Excel
  • Tạo một công thức đơn giản

    Để xem ví dụ về công thức đơn giản, chúng ta hãy chuyển đổi một giá trị văn bản thành kiểu chữ thích hợp bằng cách dùng công thức Text.Proper() .

    1. Trong thanh công thức Trình soạn thảo Truy vấn, hãy nhập = Text.Proper(“text value”), và nhấn Enter hoặc chọn biểu tượng Enter.

    2. Power Query cho bạn thấy kết quả trong ngăn kết quả công thức.

    3. Để xem kết quả trong trang tính Excel, hãy chọn Đóng & Tải.

    Kết quả sẽ trông như thế này trong một trang tính:

    Bạn cũng có thể tạo công thức truy vấn nâng cao trong Trình soạn thảo Truy vấn.

    Tạo công thức nâng cao

    Để xem ví dụ về công thức nâng cao, chúng ta hãy chuyển đổi văn bản trong một cột thành kiểu chữ thích hợp bằng cách kết hợp nhiều công thức. Bạn có thể sử dụng Ngôn ngữ Công thức Power Query để kết hợp nhiều công thức thành các bước truy vấn có kết quả tập dữ liệu. Có thể nhập kết quả vào một trang tính Excel.

    Chẳng hạn, giả sử bạn có một bảng Excel chứa các tên sản phẩm mà bạn muốn chuyển thành kiểu chữ thích hợp.

    Bảng ban đầu trông như thế này:

    Và bạn muốn bảng kết quả trông giống như thế này:

    Chúng ta hãy xem qua các bước công thức truy vấn để thay đổi bảng ban đầu sao cho các giá trị trong cột Tên Sản phẩm có kiểu chữ thích hợp.

    Ví dụ về truy vấn nâng cao bằng Trình soạn thảo Nâng cao

    • Tạo một chuỗi các bước công thức truy vấn bắt đầu bằng câu lệnh let. Vui lòng lưu ý rằng Ngôn ngữ Công thức Power Query có phân biệt chữ hoa, chữ thường.

    • Mỗi bước công thức truy vấn xây dựng trên bước trước đó bằng cách tham chiếu đến một bước theo tên.

    • Tạo đầu ra cho công thức truy vấn bằng câu lệnh in. Thông thường, bước truy vấn sau cùng được dùng làm kết quả tập dữ liệu cuối cùng.

    Bước 1 – Mở Trình soạn thảo Nâng cao

    1. Trong Trình soạn thảo Truy vấn, hãy chọn Trình soạn thảo Nâng cao.

    2. Bạn sẽ thấy Trình soạn thảo Nâng cao.

    Bước 2 – Xác định nguồn ban đầu

    Trong Trình soạn thảo Nâng cao:

    1. Dùng câu lệnh let gán Source = công thức Excel.CurrentWorkbook(). Thao tác này sẽ sử dụng bảng Excel làm nguồn dữ liệu. Để biết thêm thông tin về công thức Excel.CurrentWorkbook(), hãy xem Excel.CurrentWorkbook.

    2. Gán Source cho kết quả in.

      letSource = Excel.CurrentWorkbook(){ inSource

    3. Truy vấn nâng cao của bạn sẽ trông giống như thế này trong Trình soạn thảo Nâng cao.

    4. Để xem kết quả trong một trang tính:

      1. Bấm Xong.

      2. Trong ribbon Trình soạn thảo Truy vấn, bấm Đóng & Tải.

    Kết quả sẽ trông như thế này trong một trang tính:

    Bước 3 – Tăng cấp hàng đầu tiên thành tiêu đề

    Để chuyển đổi các giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, trước tiên bạn cần tăng cấp hàng đầu tiên thành tiêu đề cột. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:

    1. Thêm #”First Row as Header” = công thức Table.PromoteHeaders() vào các bước công thức truy vấn của bạn và tham chiếu đến Source là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.PromoteHeaders(), hãy xem Table.PromoteHeaders.

    2. Gán #”First Row as Header” cho kết quả in.

      let Source = Excel.CurrentWorkbook(){, #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"

    Kết quả sẽ trông như thế này trong một trang tính:

    Bước 4 – Thay đổi mỗi giá trị trong một cột thành kiểu chữ thích hợp

    Để chuyển đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, bạn dùng Table.TransformColumns() và tham chiếu đến bước công thức truy vấn “First Row as Header”. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:

    1. Thêm #”Capitalized Each Word” = công thức Table.TransformColumns() vào các bước công thức truy vấn của bạn và tham chiếu đến #”First Row as Header” là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.TransformColumns(), hãy xem Table.TransformColumns.

    2. Gán #”Capitalized Each Word” cho kết quả in.

    let Source = Excel.CurrentWorkbook(){, #"First Row as Header" = Table.PromoteHeaders(Source),#"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}}) in#"Capitalized Each Word"

    Kết quả cuối cùng sẽ thay đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp và trông giống như thế này trong trang tính:

    Với Ngôn ngữ Công thức Power Query bạn có thể tạo các truy vấn dữ liệu từ đơn giản đến nâng cao để khám phá, kết hợp và tinh chỉnh dữ liệu. Để tìm hiểu thêm về Power Query, hãy xem Trợ giúp Microsoft Power Query cho Excel.

    --- Bài cũ hơn ---

  • Cách Gộp Dữ Liệu Từ Nhiều Bảng Vào 1 Bảng Sử Dụng Power Query
  • Cách Dùng Hàm Dcount Trên Excel
  • Cách Dùng Hàm Countif Và Countifs Với Nhiều Điều Kiện
  • Sử Dụng Công Thức Và Hàm Trong Excel
  • Các Hàm Thống Kê Trong Excel Có Điều Kiện, Theo Thời Gian, Số Lượng
  • Power Pivot And Power Bi: The Excel User’s Guide To Dax, Power Query, Power Bi & Power Pivot In Excel 2010

    --- Bài mới hơn ---

  • Hướng Dẫn Cách Cài Đặt Power Query Trong Excel 2010, 2013, 2022
  • Hướng Dẫn Cách Cài Đặt Power Pivot Trong Thanh Menu Của Excel
  • Enabling Power Pivot Excel 2013
  • Pivot Table Trong Excel 2007, 2010, 2013
  • Cách Lập Báo Cáo Theo Tháng Bằng Pivot Table Khi Dữ Liệu Không Có Sẵn Cột Tháng
  • Microsoft Power BI, including Power Pivot and Power Query, are a set of free add-ons to Excel that allow users to produce new kinds of reports and analyses that were simply impossible before.

    This book, printed in full-gorgeous color, gives you an overview of Power BI, Power Pivot and Power Query, and then pes into DAX formulas, the core capability of Power Pivot. Always from the perspective of the Excel audience.

    Written by the world’s foremost Power BI bloggers and practitioners, the book’s concepts and approach are introduced in a simple, step-by-step manner tailored to the learning style of Excel users everywhere. The techniques psented allow users to produce, in hours or even minutes, results that formerly would have taken entire teams weeks or months to produce.

    This book includes lessons on:-

    • difference between calculated columns and measures
    • how formulas can be reused across reports of completely different shapes
    • how to merge disjointed sets of data into unified reports
    • how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not
    • how to create time-intelligent calculations in pivot tables such as “Year over Year” and “Moving Averages” whether they use a standard, fiscal, or a complete custom calendar.
    • how to leverage Power Query to make your Power Pivot models awesome!
    • how to use Power BI Desktop and chúng tôi and how they fit into the Excel landscape

    The “pattern-like” techniques and best practices contained in this book have been developed and refined over several years of onsite training with Excel users around the world, and the key lessons from those seminars costing thousands of dollars per day are now available to you, within the pages of this easy-to-follow guide. This updated second edition covers new features introduced with Office 2022 and Power BI Desktop.

    At PowerPivotPro, I have really enjoyed working with the Power Pivot and Power BI community. This book, with Rob Collie, was an effort to reach out and help an even greater audience. Little did I know what I was signing up for 🙂

    It was hard juggling book writing with the training/consulting/travel. But I am glad we persevered. Big thanks to our IndieGoGo crowdfunding supporters, who not only made it possible to print this book in full-gorgeous color, but also inspired us to burn the night oil in writing those last chapters.

    I sincerely believe that Power Pivot and Power BI can transform the lives of Excel users worldwide – after all I was one of them. But change is hard, even when it’s for the better. We hope we can help you go from Excel to Power BI with this book and our services at PowerPivotPro.

    Power On!

    -Avi Singh

    Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data. We have repeatedly witnessed the “Power tools” transformative impact on the bottom line – far more robust and elegant than Excel alone, and much more agile and affordable than traditional BI tools. Their shared state of the art calculation engines (DAX and M) turn any PC into an analytical supercomputer, but the real secret is people – these tools are 100% learnable by today’s users of Excel. The data revolution lays not in the hands of an elite few, but in YOUR hands, and we want to help you seize that opportunity.

    Rob Collie is an entrepneur, author, and consultant. He was one of the founding engineers behind Power Pivot at Microsoft and operates the world’s leading Power Pivot/Power BI website, chúng tôi He lives in Indianapolis. Avichal Singh (Avi) is Principal Consultant at PowerPivotPro where he blogs, trains and consults on PowerPivot and Power BI. His past experience includes building large scale Power BI solutions at Microsoft. He lives in Seattle.

    --- Bài cũ hơn ---

  • Không Thể Cài Đặt Power Pivot Trong Excel 2013
  • Pivottable Là Gì? Hướng Dẫn Dùng Pivottable Trong Excel
  • Cách Sử Dụng Pivot Table Cơ Bản, Nâng Cao Để Thống Kê Dữ Liệu Báo Cáo Trong Excel
  • Tất Tần Tật Cách Sử Dụng Pivot Table Excel 2013 Từ Cơ Bản Đến Nâng Cao
  • Cách Làm Mới Dữ Liệu Bảng Pivot Trong Excel 2013
  • Cách Gộp Dữ Liệu Từ Nhiều Bảng Vào 1 Bảng Sử Dụng Power Query

    --- Bài mới hơn ---

  • Tạo Các Công Thức Power Query Trong Excel
  • Cách Áp Dụng Replace Trong Excel Hiệu Quả
  • Hướng Dẫn 3 Cách Thay Đổi Chữ Thường Thành Chữ Hoa Và Ngược Lại Trong Excel 2022, 2013, 2010
  • Một Số Phím Tắt Thông Dụng Nhất Trong Excel Không Thể Bỏ Qua
  • Hàm Frequency Trong Excel, Hàm Tính Giá Trị Thường Xuyên Xuất Hiện
  • Việc tổ chức dữ liệu trên Excel phụ thuộc rất nhiều yếu tố: đặc thù của nguồn nhập dữ liệu, đặc thù của dạng dữ liệu… nên không phải lúc nào chúng ta cũng có được 1 bảng dữ liệu đúng ý muốn ngay từ đầu. Do đó để có thể phục vụ việc lập báo cáo trong Excel được tốt hơn thì chúng ta cần phải tổ chức lại dữ liệu. Một trong những công việc thường gặp đó là gộp dữ liệu từ nhiều bảng tính (có thể trên nhiều sheet) vào 1 bảng tính (trên 1 sheet). Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query.

    Mục tiêu của chúng ta như sau:

    1. Chuẩn bị dữ liệu

    Các bạn có thể tải về file dữ liệu gốc để thực hành theo bài viết tại địa chỉ: http://bit.ly/2JBYRh1

    Power Query là 1 công cụ mới có trên Office từ các phiên bản Office 2022, Office 365 (đã tích hợp sẵn). Nếu bạn sử dụng Office phiên bản thấp hơn như 2010, 2013 thì bạn có thể tải Add-in Power Query trên hệ thống của Microsoft Office

    Link tải Power Query: https://www.microsoft.com/vi-vn/download/details.aspx?id=39379

    2. Nguyên tắc của việc gộp dữ liệu

    Để tránh các lỗi có thể xảy ra trong quá trình gộp dữ liệu thì chúng ta phải tuân thủ một số nguyên tắc sau:

    • Dữ liệu từ các bảng tính sẽ được nối tiếp nhau trong 1 bảng chung
    • Các bảng tính phải có cấu trúc giống nhau: số cột, tên các cột… để đảm bảo nguyên tắc nối tiếp nhau
    • Các bảng tính phải bao gồm dòng tiêu đề và hạn chế dòng trống xen kẽ dữ liệu (đảm bảo tính liên tục của dữ liệu)

    3. Cách thực hiện gộp dữ liệu bằng Power Query

    a. Chuyển các bảng cần gộp về dạng Table Data

    Tại mỗi bảng dữ liệu thành phần chúng ta sẽ chuyển về dạng Table data bằng cách:

    • Chọn bảng dữ liệu đó
    • Sử dụng phím tắt Ctrl+T (phím tắt của chức năng Insert/Table)
    • Xác nhận việc chuyển đổi này
    • Table1 – Cửa hàng A
    • Table2 – Cửa hàng B
    • Table3 – Cửa hàng C

    Tại sao lại cần chuyển về dạng Table? Bởi trong Power Query việc kết nối dữ liệu từ các Table sẽ dễ hơn, nhanh hơn. Sau khi kết nối các bảng thì chúng ta có thể trả dạng Table về dạng Range một cách dễ dàng, không bị ảnh hưởng gì.

    b. Sử dụng Power Query kết nối các bảng Table

    Cửa sổ Power Query sẽ mở ra. Trong cửa sổ này chúng ta chọn mục Append Queries ngay trong thẻ Home

    • Let / Source: là các bảng dữ liệu nguồn. Ở đây chú ý Name'”Table1″ chính là tên của các Table đã được tạo ở trên. Chúng ta sẽ thay đổi Source này thành 3 Source ứng với 3 Table
    • Let/ Appended: là lệnh nối các bảng dữ liệu. Đặt các Source vào trong câu lệnh = Table.combine({…})
    • in / Appended: trích xuất kết quả của Appended
    • Các lệnh khác có thể bỏ qua (ví dụ như changed type)

    Do đó chúng ta sẽ sửa lại câu lệnh trên thành:

    c. Xuất dữ liệu từ Power Query về 1 Sheet kết quả

    Để trích xuất dữ liệu từ Power Query về Sheet thì chúng ta làm như sau:

      Trong cửa sổ Close & Load to chọn:

    Select how you want to view this data in your workbook: Chọn dạng dữ liệu muốn hiển thị. Thông thường có thể chọn Table

    Where do you want to put the data? Chọn nơi bạn muốn đặt bảng dữ liệu kết quả này. Nếu đã có vị trí tại 1 sheet xác định thì chọn Existing worksheet, còn nếu muốn đưa vào 1 sheet mới thì chọn New worksheet.

    Để chuyển từ dạng Table về dạng bảng thường (Range) thì chúng ta bấm vào mục Convert to range trong thẻ Table Tools

    --- Bài cũ hơn ---

  • Cách Dùng Hàm Dcount Trên Excel
  • Cách Dùng Hàm Countif Và Countifs Với Nhiều Điều Kiện
  • Sử Dụng Công Thức Và Hàm Trong Excel
  • Các Hàm Thống Kê Trong Excel Có Điều Kiện, Theo Thời Gian, Số Lượng
  • Các Hàm Excel Cơ Bản Trong Kế Toán
  • Cách Sử Dụng Hàm Query Trong Google Sheets

    --- Bài mới hơn ---

  • Power Query Là Gì? Tại Sao Cần Học Power Query?
  • Hướng Dẫn Cách Sử Dụng Hàm Round Trong Excel
  • Tổng Hợp Toàn Bộ Cách Làm Tròn Số Trong Excel 365, Excel 2022, Excel 2022, Excel 2013, Excel 2010, Excel 2007, Excel 2003 Và Google Sheets
  • Cách Tạo Dữ Liệu Ngẫu Nhiên Tự Động Trong Excel Với Vlookup Randbetween
  • Tạo Ngày Tháng, Mật Khẩu, Chuỗi Văn Bản Ngẫu Nhiên Bằng Hàm Rand Và Randbetween
  • Nếu cần thao tác với dữ liệu trong Google Sheets, hàm QUERY có thể giúp ích cho bạn! Nó mang lại khả năng tìm kiếm kiểu cơ sở dữ liệu mạnh mẽ cho bảng tính, vì vậy bạn có thể tra cứu và lọc dữ liệu theo bất kỳ định dạng nào bạn muốn. Bài viết sau đây sẽ hướng dẫn bạn cách sử dụng hàm QUERY trong Google Sheets.

    Sử dụng hàm QUERY

    Hàm QUERY không quá khó để thành thạo nếu bạn đã từng tương tác với cơ sở dữ liệu bằng SQL. Định dạng của một hàm QUERY điển hình tương tự như SQL và mang lại sức mạnh của chức năng tìm kiếm cơ sở dữ liệu cho Google Sheets.

    Định dạng của một công thức sử dụng hàm QUERY là:

    =QUERY(data, query, headers)

    Thay thế “data” bằng phạm vi ô (ví dụ, “A2:D12” hoặc “A:D”).

    Đối số “headers” tùy chọn đặt số lượng hàng tiêu đề sẽ bao gồm ở đầu phạm vi dữ liệu. Nếu bạn có một tiêu đề trải rộng trên hai ô, như First trong A1Name trong A2, thì QUERY sẽ sử dụng nội dung của hai hàng đầu tiên làm tiêu đề kết hợp.

    Trong ví dụ bên dưới, một trang tính (được gọi là “Staff List”) của bảng tính Google Sheets bao gồm danh sách nhân viên. Nó chứa tên, số ID nhân viên, ngày sinh và liệu họ có tham dự buổi đào tạo nhân viên bắt buộc hay không.

    Trên trang tính thứ hai, bạn có thể sử dụng công thức QUERY để lấy danh sách tất cả các nhân viên không tham dự buổi đào tạo bắt buộc. Danh sách này sẽ bao gồm số ID nhân viên, tên, họ và việc họ có tham gia buổi đào tạo hay không.

    Để thực hiện việc này với dữ liệu được hiển thị ở trên, bạn có thể nhập:

    =QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'")

    Điều này truy vấn dữ liệu từ phạm vi từ A2 đến E12 trên trang “Staff List”.

    Giống như một truy vấn SQL thông thường, hàm QUERY chọn các cột để hiển thị ( SELECT) và xác định các tham số cho tìm kiếm ( WHERE). Nó trả về các cột A, B, C và E danh sách tất cả các hàng khớp, trong đó giá trị trong cột E ( “Attended Training”) là một chuỗi văn bản có nội dung “No”.

    Như được trình bày ở trên, 4 nhân viên trong danh sách đã không tham dự buổi tập huấn. Hàm QUERY đã cung cấp thông tin này, cũng như các cột khớp để hiển thị tên và số ID nhân viên trong một danh sách riêng.

    Ví dụ này sử dụng một phạm vi dữ liệu rất cụ thể. Bạn có thể thay đổi để truy vấn tất cả dữ liệu trong cột A sang E. Điều này sẽ cho phép bạn tiếp tục thêm nhân viên mới vào danh sách. Công thức QUERY bạn đã sử dụng cũng sẽ tự động cập nhật bất cứ khi nào bạn thêm nhân viên mới hoặc khi ai đó tham gia buổi đào tạo.

    Công thức chính xác để thực hiện điều này là:

    =QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'")

    Công thức này bỏ qua tiêu đề ban đầu “Employees” trong ô A1.

    Nếu bạn thêm nhân viên thứ 11, không tham gia khóa đào tạo vào danh sách ban đầu, như được hiển thị bên dưới ( Christine Smith), thì công thức QUERY cũng sẽ cập nhật và hiển thị nhân viên mới.

    Công thức QUERY nâng cao

    Hàm QUERY rất linh hoạt. Nó cho phép bạn sử dụng các phép toán logic khác (như ANDOR) hoặc những hàm của Google (chẳng hạn COUNT) như một phần của tìm kiếm. Bạn cũng có thể sử dụng các toán tử so sánh (lớn hơn, nhỏ hơn, v.v…) để tìm giá trị giữa 2 số liệu.

    Sử dụng toán tử so sánh với QUERY

    Bạn có thể sử dụng QUERY với các toán tử so sánh (như nhỏ hơn, lớn hơn hoặc bằng) để thu hẹp và lọc dữ liệu. Để làm điều này, bài viết sẽ thêm một cột ( F) vào bảng “Staff List”, với số lượng giải thưởng mà mỗi nhân viên đã giành được.

    Sử dụng QUERY, ta có thể tìm kiếm tất cả các nhân viên đã giành được ít nhất một giải thưởng. Định dạng cho công thức này là:

    Ví dụ trên cho thấy hàm QUERY trả về danh sách 8 nhân viên đã giành được một hoặc nhiều giải thưởng. Trong tổng số 11 nhân viên, có 3 người chưa bao giờ giành được giải thưởng.

    Sử dụng AND và OR với QUERY

    Các hàm toán tử logic lồng nhau như ANDOR hoạt động tốt trong công thức QUERY lớn hơn, để thêm nhiều tiêu chí tìm kiếm vào công thức.

    Định dạng cho công thức này là:

    Công thức này cũng sử dụng hàm DATE bổ sung để phân tích timestamp ngày một cách chính xác và tìm kiếm tất cả các ngày sinh trong khoảng từ ngày 1 tháng 1 năm 1980 đến ngày 31 tháng 12 năm 1989.

    Như đã trình bày ở trên, 3 nhân viên sinh năm 1980, 1986 và 1983 đáp ứng các yêu cầu này.

    Bạn cũng có thể sử dụng OR để tạo ra kết quả tương tự. Nếu sử dụng cùng một dữ liệu, nhưng chuyển đổi ngày và sử dụng OR, ví dụ có thể loại trừ tất cả các nhân viên sinh vào những năm 1980.

    Định dạng cho công thức này sẽ là:

    Trong số 10 nhân viên ban đầu, có 3 người được sinh ra vào những năm 1980. Ví dụ trên cho thấy 7 người còn lại, những người được sinh ra trước hoặc sau những ngày đã loại trừ.

    Sử dụng COUNT với QUERY

    Thay vì chỉ đơn giản là tìm kiếm và trả lại dữ liệu, bạn cũng có thể trộn QUERY với các hàm khác, như COUNT, để thao tác với dữ liệu. Giả sử, ví dụ muốn xóa một số nhân viên trong danh sách những người đã và đã tham gia khóa đào tạo bắt buộc.

    Để thực hiện việc này, bạn có thể kết hợp QUERY với COUNT như sau:

    =QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E")

    Nhìn vào cột E ( “Attended Training”), hàm QUERY đã sử dụng COUNT để đếm số lần mỗi loại giá trị (chứa chuỗi văn bản Yes hoặc No). Từ danh sách ví dụ, có 6 nhân viên đã hoàn thành khóa đào tạo và 4 người vẫn chưa.

    Bạn có thể dễ dàng thay đổi công thức này và sử dụng nó với các loại hàm khác của Google, như hàm SUM trong Google Sheet.

    --- Bài cũ hơn ---

  • Hướng Dẫn Sử Dụng Thành Thạo Hàm Query Trong Google Sheet
  • Cách Tính Các Hàm Số Lượng Giác Trong Excel, Radians, Degrees, Cos
  • Ký Hiệu Phi Là Gì? Cách Viết Ký Hiệu Phi (Ø) Word, Excel, Cad
  • Hướng Dẫn Sử Dụng Pivot Table Trong Excel Dành Cho Dân ‘chuyên’ Làm Báo Cáo
  • Hướng Dẫn Cách Sử Dụng Pivot Table Trong Excel
  • Conditional Merge In Power Query

    --- Bài mới hơn ---

  • Cách Tính Tiền Thưởng Và Hoa Hồng Đơn Giản Với Vlookup () Trong Excel
  • Cách Lập Sổ Quỹ Tiền Mặt Trên Excel Bằng Mẫu Sổ Có Công Thức
  • Hướng Dẫn Về Chức Năng Tìm Và Thay Thế (Find&replace) Trong Excel
  • Cách Sử Dụng Hàm Replace Để Thay Thế Đoạn Văn Bản Trong Excel
  • 3 Các Nhanh Chóng Giúp Bạn Xóa Khoảng Trắng Thừa Trong Excel
  • In this example I will use the WideWorldImportersDW sample database Microsoft offers. You should have a copy of SQL Server installed to play with for Power BI. The Developer version of SQL Server is 100% functional and free forever. You just cannot use it in production.

    I am going to use two tables for this example, Fact.Sales and Dim.Customer. I only want my Fact Sales table to have customer info for the Buying Group Tailspin Toys. That info is in the Dim Customer table. I can do this several ways.

    1. Do the merge, expand the Buying Group column from the Customer table, then filter to only show those rows.

    2. Pre-filter the Customer table for Tailspin Toys in the Buying Group column, then do the merge. It would need to be an Inner Join, otherwise you will get nulls in the Sales table when you expand, and then you have to filter those out. Which you can do, but it is more steps.

    3. You can bypass all of that and do it in one step.

    I’m not going to bore you with how to do methods 1 and 2. Let’s do method 3, a conditional join!

    First, create the merge as you normally would. In the Sales table, select Merge Queries from the ribbon, then select the Customer table. Select the Customer Key fields in both. Also make sure this is an inner join so it will remove all records from the Sale Table that are not Tailspin Toys. Your Merge dialog box will look like this:

    Table.NestedJoin has the following parameters per MS documentation:

    We want to tweak 3rd parameter – table2, which is our Customer table, We don’t want the full table2, we only want table2, filtered for Topspin Toys in the Buying Group field.

    So in the code above, we need to replace the 3rd parameter which is just a reference to #”Dimension Customer” – the name of the Customer Table. (It is just Dimension Customer – the #”name here” syntax is just how Power Query references objects with spaces and special characters.)

    We want to replace that with this code:

    Table.SelectRows(#”Dimension Customer”, each [Buying Group] = “Tailspin Toys”)

    Since 100% of the work is done on the SQL Server, it will be very fast compared to how it would run if the Power Query mashup engine on your PC or On-Premise Gateway would process it.

    If you are using some other source, like text files or Excel files, folding won’t happen of course, but it should still perform well as this is single filter applied, not a filter applied for every row of either table.

    I cannot say that this method is any faster than doing it the longer ways (methods #1 and #2 above) but it is shorter code. Plus, you can get fancier with the Table.SelectRows() function we used by filtering on multiple fields using and/or criteria. You may break folding if you get too fancy and the Power Query engine cannot p the SQL out, so be careful. If performance is a must, methods #1 or #2 are more likely to fold with many filters in the condition, but you won’t know until you try. Happy Querying!

    --- Bài cũ hơn ---

  • How To Combine Tables With Power Query
  • How To Merge Tables With Power Query
  • Hướng Dẫn Về Chức Năng Power Query Trong Power Pivot
  • Hướng Dẫn Cách Sử Dụng Power Query Để Nạp Dữ Liệu Vào Power Pivot
  • How To Create A Microsoft Query In Excel (Excel Query)
  • Web hay
  • Links hay
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100