Cách Cài Đặt Power Query Trong Excel 2010, Excel 2013, Excel 2022

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

  • Chuẩn Hoá Kiểu Dữ Liệu “khó Đỡ” Với Công Cụ Power Query Trong Excel
  • Khóa Học Phân Tích Báo Cáo Bằng Power Query Trong Excel Để Bán Hàng Thành Công
  • Cách Tính Căn Bậc Hai Trong Excel (Hàm Sqrt, Power Và Lũy Thừa)
  • Hướng Dẫn Cách Xuống Hàng Trong Excel 2010 2022
  • Find Và Replace Nâng Cao Trong Excel
  • Power Query là một addin do chính Microsoft phát triển dành cho Excel dùng cho việc xử lý dữ liệu và tải dữ liệu vào trong Excel để xử lý. Power Query có thể hỗ trợ Excel xử lý hơn giới hạn 1 triệu dòng trên 1 bảng tính một cách nhanh chóng và hiệu quả. Bài viết nãy sẽ hướng dẫn chi tiết cách bạn có thể cách cài đặt Power Query trong Excel.

    Từ phiên bản Excel 2022 tới những phiên bản mới hơn như Excel 2022, Excel 365 thì Power Query đã được cài sẵn trong Excel. Đối với Excel 2010, Excel 2013, để sử dụng Power Query thì bạn cần phải cài đặt thêm. Các bước cài đặt như sau:

    Bước 1: Xác định phiên bản Excel của bạn đang dùng là bản 32bits hay 64bits

    Làm thế nào để biết Excel 2010 của bạn là 32bits hay 64bits? Các bạn hãy theo các bước như sau:

    Làm thế nào để biết Excel 2013 của bạn là 32bits hay 64bits? Các bạn hãy theo các bước như sau:

    Cách cài đặt Power Query trong Excel

    Sau khi đã biết mình đang sử dụng phiên bản Excel 32bits hay 64bits, bạn hãy truy cập vào đường link để download Power Query:

    Truy cập vào đường link download Microsoft Power Query for Excel

    Ở trang Web này, bạn chọn ngôn ngữ. Thường thì chúng ta sẽ dùng tiếng anh, nên ở mục Select Language chúng ta sẽ chọn English rồi bấm nút Download.

    Sau đó ở cửa sổ tiếp theo, bạn chọn phiên bản tương ứng với số bits trên Excel của bạn rồi bấm Next để có thể download.

    Chú ý nếu bạn đang cài đặt trên máy tính ở công ty, có thể bạn sẽ cần nhờ tới sự giúp đỡ của bộn phận IT để cài đặt thêm Addin này.

    Sau khi cài đặt thành công, trên thanh Ribbon Excel của bạn sẽ xuất hiện thêm 1 thẻ nữa là thẻ Power Query.

    Power Query ở đâu trên Excel 2022, Excel 2022 và Excel 365

    Hướng dẫn cách xoá bỏ giá trị trùng lặp với Power Query Hướng dẫn cách xoá bỏ giá trị trùng lặp với Power Query Chuẩn hoá kiểu dữ liệu “Khó đỡ” với công cụ Power Query trong Excel

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

  • 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
  • Pivot Table Trong Excel 2007, 2010, 2013
  • Enabling Power Pivot Excel 2013
  • Hướng Dẫn Cách Cài Đặt Power Pivot Trong Thanh Menu Của Excel
  • 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 Query Trong Excel 2010, 2013, 2022

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

  • 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
  • Cách Cài Đặt Power Query Trong Excel 2010, Excel 2013, Excel 2022
  • Power Query là một công cụ hữu ích trong Excel trong việc tái cấu trúc dữ liệu (hay còn gọi là Add-in Power Query). Tuy nhiên trong một số phiên bản Excel không có sẵn công cụ này. Do đó để sử dụng được Power Query, Học Excel Online sẽ hướng dẫn các bạn cách cài đặt Power Query trong Excel, áp dụng cho các phiên bản Microsoft Office 2010, 2013, 2022, Office 365.

    Power Query là gì? Tại sao cần học Power Query?

    Cách cài đặt Power Query trong Excel 2010, 2013

    Trong Excel phiên bản 2010 và 2013 thì không có sẵn Power Query. Do đó bạn cần phải tải Add-in này về và tiến hành cài đặt thì mới sử dụng được.

    Các bước thực hiện như sau:

    Bước 1: Kiểm tra phiên bản Excel để lựa chọn phiên bản cài đặt phù hợp

    Power Query có 2 phiên bản cài đặt là bản 32 bit và bản 64 bit. Do đó bạn cần kiểm tra xem Excel bạn đang sử dụng là phiên bản nào, khi đó bạn mới biết nên cài đặt phiên bản Power Query nào cho phù hợp.

    • Với Excel phiên bản 2010, các bạn vào thẻ File, chọn mục Help sẽ có thông tin phiên bản Excel (mục Version)
    • Với Excel phiên bản 2013, 2022: Các bạn vào thẻ File, mục Account, chọn About Excel

    Bước 2: Đóng các file Excel lại, sau đó vào link tải Add-in Power Query của Microsoft để chọn phiên bản phù hợp rồi bấm nút Download

    Link: https://www.microsoft.com/en-us/download/details.aspx?id=39379

    Bước 3: Sau khi tải thành công, bạn tiến hành cài đặt bằng cách bấm vào bộ cài, nhấn Next và chờ chương trình cài đặt thành công.

    Bước 4: Sau khi hoàn thành, trên giao diện Excel sẽ xuất hiện thêm thẻ Power Query. Trong thẻ này sẽ có chứa các tính năng của Power Query

    Cách sử dụng Power Query trong Excel 2022 và Office 365

    Trong 2 phiên bản này, Microsoft đã xây dựng Power Query thành dạng Built-in, tức là tích hợp sẵn trong bộ cài Microsoft Office. Do đó bạn có thể sử dụng ngay mà không cần cài đặt thêm gì. Công cụ Power Query tích hợp sẵn này được đặt tại thẻ Data, trong 2 nhóm Get & Transform DataQueries & Connections

    Tham khảo bài viết:

    Hướng dẫn cách nạp dữ liệu từ Excel vào Power BI Hướng dẫn cách nạp dữ liệu từ file PDF vào Power BI Desktop

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

  • Power Pivot And Power Bi: The Excel User’s Guide To Dax, Power Query, Power Bi & Power Pivot In Excel 2010
  • 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
  • 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
  • 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)
  • Join Two Or More Tables In Excel With Power Query

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

  • Hàm Chia Lấy Số Dư Mod Và Chia Lấy Số Nguyên Quotient Trong Excel
  • How To Use The Excel Roundup Function
  • 99 Phím Tắt Trong Excel
  • Những Phím Tắt Hay Dùng Trong Excel, Dân Văn Phòng Không Nên Bỏ Qua
  • How To Randomize A List In Excel: Sort Randomly Cells, Rows And Columns
  • In this tutorial, we will look at how you can join tables in Excel based on one or more common columns by using Power Query and Merge Tables Wizard.

    Combining data from multiple tables is one of the most daunting tasks in Excel. If you decide to do it manually, you may spend hours only to find out that you’ve messed up important information. If you are an experienced Excel pro, then you can possibly rely on VLOOKUP and INDEX MATCH formulas. A macro, you believe, could do the job in no time, if only you knew how. The good news for all Excel users – Power Query or Merge Tables Wizard can be your time-saver. The choice is yours.

    How to join tables with Excel Power Query

    In simple terms, Power Query (also known as Get & Transform in Excel 2022 and Excel 2022) is a tool to combine, clean and transform data from multiple sources into the format you need such as a table, pivot table or pivot chart.

    Among other things, Power Query can join 2 tables into 1 or combine data from multiple tables by matching data in columns, which is the focus of this tutorial.

    For the results to meet your expectations, please keep in mind the following things:

    • Power Query is a built-in feature in Excel 2022 and Excel 2022, but it can also be downloaded in Excel 2010 and Excel 2013 and used as an add-in. In earlier versions, some windows may look different from the images in this tutorial that were captured in Excel 2022.
    • For the tables to be combined correctly, they should have at least one common column (also referred to as a common id or key column or unique identifier). Also, the common columns should contain only unique values, with no repeats.
    • The source tables can be located on the same sheet or in different worksheets.
    • Unlike formulas, Power Query does not pull data from one table to another. It creates a new table that combines data from the original tables.
    • The resulting table does not update automatically. You should explicitly tell Excel to do this. Please see how to refresh a merged table.

    Source data

    As an example, let’s join 3 tables based on the common columns Order ID and Seller. Please note that our tables have different numbers of rows, and although table 1 has duplicates in the Seller column, table 3 contains only unique entries.

    Our task is to map the data in table 1 with the relevant records from the other two tables, and combine all the data into a new table like this:

    • Table 1 is named Orders
    • Table 2 is named Products
    • Table 3 is named Commissions

    Create Power Query connections

    Not to clutter your workbook with copies of your original tables, we are going to convert them into connections, do the merge within the Power Query Editor, and then load only the resulting table.

    To save a table as a connection in Power Query, here’s what you do:

    1. Select your first table (Orders) or any cell in that table.
    2. This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.

    3. Repeat the above steps for all other tables you want to merge (two more tables, Products and Commissions, in our case).

    When finished, you will see all the connections on the pane:

    Merge two connections into one table

    With the connections in place, let’s see how you can join two tables into one:

    1. In the Merge dialog box, do the following:
      • Select your 1st table (Orders) from the first drop-down.
      • Select your 2nd table (Products) from the second drop-down.
      • In the Join Kind drop-down list, leave the default option: Left Outer (all from first, matching from second).

    Upon completion of the above steps, the Power Query Editor will show your first table (Orders) with one additional column named like your second table (Products) added to the end. This additional column does not have any values yet, just the word “Table” in all the cells. But don’t feel discouraged, you did everything right, and we are going to fix that in a moment!

    Select the columns to add from the second table

    At this point, you have a table resembling the one in the screenshot below. To complete the merging process, perform the following steps within the Power Query Editor:

    1. In the box that opens, do this:
      • Keep the Expand radio button selected.
      • Unselect all columns, and then select only the column(s) you want to copy from the second table. In this example, we select only the Product column because our first table already has Seller and Order ID.
      • Uncheck the Use original column name as pfix box (unless you want the column name to be pfixed with the table name from which this column is taken).

    As the result, you will get a new table that contains every record from your first table and the additional column(s) from the second table:

    If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.

    Merge more tables (optional)

    In case you have three or more tables to join, there is some more work for you to do. I will outline the steps briefly here, because you have already done all this when joining the first two tables:

    1. Save the table you’ve got in the pvious step (shown in the screenshot above) as a connection:

    2. The screenshot below shows my settings:

    In this example, we add only the Commission column:

    As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.

    Import the merged table to Excel

    With the resulting table in the Power Query Editor, there is just one thing left for you to do – load it in your Excel workbook. And it is the easiest part!

    1. In the Import Data dialog box, select Table and New Worksheet options.

    A new table combining the data from two or more sources appears in a new worksheet. Congratulations, you did it!

    As a finishing touch, you may want to apply the right number format to some columns and maybe change the default table style to your favorite one. After these improvements, my combined table looks very nice:

    In the pvious example, we were combining tables by matching data in one key column. But there is nothing that would pvent you from selecting two or more column pairs. Here’s how:

    After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.

    Merge Tables Wizard – quick way to join 2 tables in Excel

    Now that you are familiar with the inbuilt tool, let me show you our approach to merging tables in Excel.

    In this example, we will be combining the same tables that we joined with Power Query a moment ago. I have just added a few more rows to the second table to show you more capabilities of our add-in:

    With the Merge Tables Wizard installed in your Excel, here’s what you need to do:

      Please notice the Case-sensitive matching box at the top. Select it if you want to treat uppercase and lowercase text in the key columns as different characters. For this example, we don’t need that, so we leave the box unselected.

      We select the Seller column because we have more rows in the second table and we want the new seller names to appear in the existing Seller column:

    1. This step is very important because it determines how your tables will be merged.

      In this example, we go with the default options shown in the screenshot below. But I’d like to draw your attention to the following 2 boxes that can pvent overwriting your existing data in case you’ve chosen to update some columns:

      • Empty cells only
      • Only if cells in the lookup table contain data

    With the default options, the wizard highlights the newly added rows and adds the Status column. If you don’t want any of that, clear the corresponding boxes in the last step.

    To join three and more tables, simply repeat the above steps. Just remember to select the result of a pvious merge as your main table.

    This example has shown just one scenario that our wizard can handle, but there is much more to it! If you are curious to know other use cases, please check out these examples.

    Also, you can download a a trial version of Ultimate Suite for Excel that includes Merge Tables Wizard as well as 60+ other useful tools.

    In case you are looking to join tables in some other way, you may find the following resources useful.

    Other ways to combine data in Excel:

    Merge tables by column headers – join two or more tables based on column names. You can choose to combine all the columns or only the ones you select.

    Combine multiple worksheets into one – copy multiple sheets into one summary worksheet. Of course, it’s not manual copy/pasting! You only indicate which worksheets to merge, and our Copy Sheets tool does the rest.

    Compare two Excel files – how to compare two tables (worksheets) for differences and merge them into a single sheet.

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

  • Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu
  • Power Query Overview: An Introduction To Excel’S Most Powerful Data Tool
  • How To Delete Pivot Table
  • How To Refresh Pivot Table On File Open In Excel?
  • Hướng Dẫn Cách Để Làm Mới Một Pivot Table Trong Excel
  • How To Install Power Query

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

  • How To Use Find And Replace In Microsoft Word To Make Quick Edits To A Document
  • Hướng Dẫn Cách Sử Dụng Hàm Replace Trong Excel 2007, 2010, 2013, 2022
  • Cách Sử Dụng Excel 2022 – Cách Dùng Find, Select & Replace Trong Excel
  • Search And Replace In Excel Workbooks—Online Help
  • Cách Lấy Số Ngẫu Nhiên Không Trùng Trong Excel
  • Power query is a great tool built by Microsoft that will help you work with data in Excel. This tool is great for connecting to various external data sources, querying and transforming data, or cleaning and parsing data.

    • Web pages, Facebook
    • Excel, CSV, XML, Text or Hadoop (HDFS) Files
    • A Folder
    • Various databases like MS Access, SQL Server, MySQL, Microsoft Azure SQL, Oracle, IBM DB2, PostgreSQL, Sybase, Teradata, OData etc…

    This is available as an add-in for excel 2010 professional plus or 2013 and comes already built in for Excel 2022.

    You can download Excel Power Query here from Microsoft.

    Unfortunately, if you’re not running Excel 2010 professional plus or 2013, then you will need to upgrade to Excel 2022 in order to use this feature as it’s not available for pvious versions of Excel. Mac user are also out of luck.

    There are both a 32-bit and 64-bit versions and which one you choose will depend on the version of Excel which you have installed.

    Excel 2010

    To check what version you have:

    1. Go to the “File” tab.
    2. Go to the “Help” section.
    3. Here you will see the product version, if it says professional plus 2010, then you’re in luck.
    4. Here you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.

    Excel 2013

    To check what version you have:

    1. Go to the “File” tab.
    2. Go to the “Account” section.
    3. Here you will see the product version.
    4. In the screen that pops up, at the top you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.

    Excel 2022

    Power query comes p-installed in Excel 2022 but has been renamed to “Get & Transform” and is under the Data tab in the ribbon. If you have Excel 2022, then you don’t need to do anything to use it.

    Download The Add-In

    Go to the Microsoft website:

    1. Select your pferred language.

    Select The Correct Version

    1. Select either the 32-bit or 64-bit version depending on your version of Excel.

    Run The Setup Wizard

    Follow The Setup

    Follow the steps in the Setup Wizard.

    Power Query Is Now Ready To Use

    Now the next time you open up Excel, Power Query will be available to use under its own tab.

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

  • Cách Xóa Pivottable Trong Excel
  • Introduction To Power Pivot – Excel Exposure
  • Excel 2022: Intro To Pivottables
  • Danh Sách Tổng Hợp Các Phím Tắt Excel 2022
  • Không Mở Được File Excel
  • Power Query Là Gì? Tại Sao Cần Học Power Query?

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

  • 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
  • Cách Sử Dụng Hàm Left, Right, Mid, Len Trong Excel
  • Trước đây tôi cũng giống như bạn, đó là chẳng cần biết Power Query là gì. Tôi cứ sử dụng Excel một cách tự nhiên, vô thức. Có những lúc công việc trở nên rất phức tạp: Khi phải làm sạch 1 bảng dữ liệu từ người khác gửi (hay trích xuất từ phần mềm ra, hoặc tải từ trên mạng về), khi thì phải gom dữ liệu từ nhiều file Excel vào chung 1 file… Những việc đó khiến tôi mất nhiều thời gian. Muốn tự động những việc này, tôi có tìm hiểu cả VBA. Nhưng để dùng được VBA làm những việc này thì cũng mất khá nhiều thời gian. Tôi tự hỏi: có cách nào giúp làm những việc này trở nên dễ dàng hơn không?

    Hóa ra giải pháp đã có sẵn rồi, chỉ là tôi không chú ý tới nó. Giải pháp chính là Power Query.

    Power Query là gì?

    Power Query là công cụ giúp tạo ra 1 quy trình tự động cho việc tổ chức, xử lý, tái cấu trúc lại bảng dữ liệu.

    Trong các phiên bản Office 2013 trở về trước (2010, 2013), bạn phải cài đặt Power Query dưới dạng 1 Add-in (tiện ích mở rộng).

    Từ phiên bản Office 2022 trở về sau (2016, 2022, 365), Power Query đã được tích hợp sẵn trong Excel: Nhóm Get & Transform Data trong thẻ Data

    Khi được hỏi ” điều gì là quan trọng nhất” khi làm việc trên Excel, tôi sẽ nói ngay rằng: Cần chắc chắn rằng dữ liệu đã được làm sạch và thống nhất. Điều này bao gồm:

    • Cách cấu trúc bảng dữ liệu đúng. Mỗi khi bế tắc trong việc tính toán với dữ liệu, tôi đều nghĩ tới việc “cấu trúc lại”. Khi cấu trúc lại thì hóa ra việc tính toán lại trở nên vô cùng đơn giản.
    • Các giá trị được nhập vào là đúng về loại dữ liệu, về nội dung và được cập nhật liên tục
    • Các bảng dữ liệu cùng 1 chức năng cần được tập hợp thống nhất trong 1 bảng. Việc này cũng cần được làm 1 cách tự động.

    Cách cấu trúc bảng dữ liệu đúng

    Việc cấu trúc bảng dữ liệu đúng cách có thể giúp ích rất nhiều:

    • Có thể sử dụng hàm SUMIFS, COUNTIFS để tính toán, báo cáo theo nhiều điều kiện một cách dễ dàng
    • Có thể trích lọc dữ liệu chi tiết bằng Advanced Filter một cách dễ dàng
    • Sử dụng được Pivot Table để làm các báo cáo động, báo cáo phân tích dữ liệu…

    Điều này dẫn tới 1 khó khăn là: phải cấu trúc lại thì mới tính ra kết quả như ý muốn được.

    Thử hình dung bạn phải tổ chức lại 1 cách thủ công, copy paste từng phần, rồi phải xoay chuyển cách biểu diễn các nội dung… quả là 1 công việc khó khăn và tốn thời gian.

    Power Query giúp bạn dễ dàng cấu trúc lại bảng dữ liệu. Đặc biệt hơn nữa là những gì bạn làm trong Power Query đều được ghi lại, giúp cho việc cập nhật thêm thông tin trong bảng dữ liệu gốc sẽ được tự động cập nhật vào bảng kết quả đã tái cấu trúc.

    Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu

    Các giá trị được nhập vào là đúng về loại dữ liệu

    Khi lấy dữ liệu từ các nguồn không phải từ Excel: Web, SQL, các phần mềm khác… thì chúng ta không để chắc chắn được về loại dữ liệu nhận được. Các dữ liệu dạng Number, Date thường bị nhầm lẫn sang dạng Text, khiến cho việc tính toán sẽ không ra kết quả đúng.

    Thông thường bạn sẽ phải sửa lại dữ liệu, định dạng lại. Nhưng mỗi khi muốn cập nhật thêm dữ liệu, bạn sẽ phải thực hiện lại các thao tác đó.

    Power Query có thể giúp bạn quy định lại dữ liệu có trong từng cột về đúng loại dữ liệu. Bạn sẽ không còn mất thời gian lo lắng cho việc này nữa. Nạp dữ liệu thô vào, chỉ cần bấm Refresh tại bảng kết quả, bạn đã có được bảng dữ liệu chính xác để làm việc rồi, hơn nữa quá trình này lại được thực hiện 1 cách tự động và rất nhanh.

    Lấy dữ liệu tài chính tự động từ Web vào Excel bằng Power Query Lấy dữ liệu trên trang Facebook cá nhân vào Excel bằng Power Query

    Tập hợp dữ liệu từ nhiều Sheet, nhiều Workbook vào 1 bảng chung

    Tôi đã gặp rất nhiều trường hợp này, đó là khi bạn phải tập hợp dữ liệu từ nhiều chi nhánh, mỗi chi nhánh 1 file Excel giống nhau, hoặc nhiều người làm chung trên 1 file excel, mỗi người 1 Sheet (hay mỗi ngày 1 Sheet). Để báo cáo được, bạn cần phải tập hợp tất cả các dữ liệu này trên 1 bảng chung. Vấn đề là các Sheet, các Workbook sẽ phát sinh thêm thường xuyên. Tập hợp lại đã khó, đảm bảo dữ liệu mới không bị thiếu, sót khi tập hợp còn khó hơn.

    Nếu không biết Power Query, bạn sẽ cần dùng tới VBA (gần như chắc chắn). Nhưng VBA trong trường hợp này cũng không hề đơn giản. Những code cần sử dụng trong trường hợp này thường là khó, phức tạp, không dễ để học và làm được trong 1 thời gian ngắn.

    Khi biết tới Power Query, tôi đã thực sự ngạc nhiên vì nó có thể giải quyết vấn đề này 1 cách triệt để. Sẽ có 1 chút câu lệnh, nhưng đều rất đơn giản, dễ học, dễ thao tác. Kết quả lại rất bất ngờ: chỉ làm 1 lần, sau này khi thêm Sheet, thêm Workbook thì vẫn tự động cập nhật vào bảng chung. Điều này thật ngoài sức tưởng tượng.

    Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query

    Power Query dễ học và dễ sử dụng

    Nếu dành chút thời gian đọc những bài tham khảo được gợi ý ở trên, bạn có thể thấy Power Query rất dễ làm. Bạn sẽ có thể dễ dàng thực hiện theo từng bước. Các bước đó sẽ được ghi lại tự động để bạn không phải làm lại các thao tác đó nữa. Vậy nên việc của bạn chỉ đơn giản là: Học thuộc lòng các thao tác đó.

    Tất nhiên sẽ có những trường hợp khó, đòi hỏi phải kỳ công trong việc xử lý. Nhưng tin tôi đi, độ khó khi làm bằng Power Query đã giảm đi rất nhiều lần so với làm bằng VBA.

    Để giúp các bạn có cái nhìn rõ hơn về tính ứng dụng của Power Query trong công việc, tôi sẽ xây dựng một số mô hình phân tích dữ liệu để làm rõ hơn nội dung này.

    Đón xem bài viết: Hướng dẫn cách xây dựng bảng dữ liệu phân tích hiệu suất lao động bằng Power Query

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

  • Cách Sử Dụng Hàm Query Trong Google Sheets
  • 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
  • Power Query Overview: An Introduction To Excel’S Most Powerful Data Tool

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

  • Power Query – Công Cụ Tự Động Hóa Quy Trình Nhập Dữ Liệu
  • Join Two Or More Tables In Excel With Power Query
  • Hàm Chia Lấy Số Dư Mod Và Chia Lấy Số Nguyên Quotient Trong Excel
  • How To Use The Excel Roundup Function
  • 99 Phím Tắt Trong Excel
  • 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…? 🙂

    Power Query works in a very similar way!

    1. You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
    2. Press buttons in the Power Query Editor window to transform your data.
    3. 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.

    What Do You Think?

    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 ---

  • How To Delete Pivot Table
  • How To Refresh Pivot Table On File Open In Excel?
  • Hướng Dẫn Cách Để Làm Mới Một Pivot Table Trong Excel
  • How To Create Pivot Chart In Excel?
  • Chương 3: Toán Tử So Sánh Trong Excel
  • 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