Xu Hướng 5/2023 # Hướng Dẫn Về Chức Năng Power Query Trong Power Pivot # Top 14 View

Bạn đang xem bài viết Hướng Dẫn Về Chức Năng Power Query Trong Power Pivot được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Trong những bài viết trước, chúng ta đã tìm hiểu về các thao tác đều được thực hiện trực tiếp trong cửa sổ làm việc của Power Pivot: từ nạp dữ liệu cho tới thay đổi các bảng. Tuy nhiên trong thực tế việc này lại khá bất tiện so với thực hiện thông qua công cụ Power Query.

Power Query là gì?

Power Query cũng là 1 add-in giống như Power Pivot nhưng được tích hợp sẵn trên Excel từ phiên bản Office 2016 là nhóm Get & Transform trong thẻ Data. Nếu máy tính của bạn đang sử dụng Office ở phiên bản thấp hơn thì bạn có thể tải Add-in này về theo địa chỉ:

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

Sau khi tải về bạn tiến hành cài đặt là sử dụng được.

Hình 1: Vị trí nhóm Get & Transform Data trên thanh công cụ tại thẻ Data

Power Query có tác dụng chính là:

Hình 2: Các tác dụng chính của Power Query

Kết nối: tạo kết nối tới dữ liệu trong nền tảng điện toán đám mây, trong một dịch vụ, hoặc cục bộ.

Chuyển đổi (tái cấu trúc): định hình dữ liệu để đáp ứng nhu cầu của bạn; dữ liệu nguồn ban đầu vẫn không thay đổi.

Kết hợp: tạo một mô hình dữ liệu từ nhiều nguồn dữ liệu và có được góc nhìn độc đáo về dữ liệu

Chia sẻ: sau khi truy vấn của bạn hoàn tất, bạn có thể lưu, chia sẻ hoặc sử dụng nó cho báo cáo

Hướng dẫn cách sửa nội dung bảng đã nạp vào Power Pivot: https://blog.gitiho.com/wp-admin/post.php?post=45384&action=edit Hướng dẫn cách sử dụng Power Query để nạp dữ liệu vào Power Pivot https://blog.gitiho.com/wp-admin/post.php?post=45417&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,…

Conditional Merge In Power Query

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.

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

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.

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

Hướng Dẫn Cách Phân Biệt Giữa Power Pivot Và Pivot Table

Thoạt đầu mới nhắc tới 2 từ này, bạn sẽ rất dễ bị nhầm lẫn bởi nó đều có chung từ Pivot (Power Pivot và Pivot Table). Nhưng nếu tìm hiểu kỹ hơn, bạn sẽ thấy 2 công cụ này trong Excel hoàn toàn khác nhau với những mục đích sử dụng riêng biệt.

Khái niệm

Pivot Table đơn thuần là một công cụ hỗ trợ công việc báo cáo trong Excel, giúp bạn tổng hợp dữ liệu một cách nhanh chóng. Sử dụng chức năng này, chúng ta có thể phân loại dữ liệu; chia dữ liệu theo từng khoảng thời gian mong muốn như năm, tháng, quí, tuần cũng như có thể tạo ra biểu đồ chỉ bằng một số thao tác kéo thả. Từ đó bạn đã có được 1 bảng báo cáo trực quan, chính xác theo những gì được tổng kết lại từ bảng dữ liệu.

Còn Power Pivot là một bổ trợ (Add-in) của Excel cần thiết khi phải phân tích dữ liệu với số lượng lớn (triệu dòng) từ nhiều nguồn khác nhau. Power Pivot cũng sử dụng một công cụ tính toán khác với Excel giúp việc phân tích dữ liệu trở nên nhanh hơn.

Power Pivot có các Biểu thức phân tích dữ liệu (DAX) hay các công thức và các hàm DAX, một yếu tố tương tự như công thức và hàm trong Excel. Ví dụ là LOOKUPVALUElà một hàm tương tự như VLOOKUP trong Excel.

Những điểm khác biệt

Tuy nhiên Pivot Table có một số nhược điểm là:

Nếu cấu trúc của bảng dữ liệu ban đầu chưa chính xác, bạn sẽ mất nhiều thời gian để chỉnh sửa, nếu không kết quả mà Pivot Table trả về sẽ không được như mong muốn.

Giới hạn của 1 bảng đơn lẻ trong Excel là khoảng 1 triệu dòng dữ liệu. Tuy nhiên trên thực tế bạn có thể sẽ phải làm việc với nhiều dữ liệu hơn nữa.

Khi bước vào làm việc với Power Pivot, bạn sẽ thấy:

Power Pivot là một công cụ mô hình hóa dữ liệu cho phép bạn chuẩn bị dữ liệu của mình theo cách mà vẫn có thể được sử dụng trong Pivot Table.

Power Pivot không thay đổi bất cứ điều gì về các Pivot Table, nhưng nó thay đổi mọi thứ khi bạn cần đến dữ liệu dạng kết nối. Power Pivot thêm phương thức thứ ba (và, theo quan điểm của tôi, là phương pháp tốt nhất) để kết nối với dữ liệu nguồn. Về mặt lý thuyết Pivot Table không có giới hạn kích thước cơ sở dữ liệu.

Power Pivot cho phép bạn nhập dữ liệu từ nhiều nguồn dữ liệu khác nhau; kết hợp các bảng dữ liệu riêng biệt với nhau một cách logic để dữ liệu có thể hoạt động cùng nhau mà không cần công thức tham chiếu (như VLOOKUP); các bảng này có thể cấu trúc theo giao diện người dùng (không cứng nhắc về cách bố cục bảng dữ liệu)

Khi mô hình dữ liệu Power Pivot đã được xây dựng và định hình, người dùng cuối cùng có thể sử dụng dữ liệu nhiều lần để nhanh chóng xây dựng nhiều báo cáo Pivot Table trong Excel.

Từ những điểm khác biệt được nêu ở trên, bạn có thể thấy Power Pivot là công cụ hỗ trợ và phát triển hoàn thiện hơn để bạn có thể tối ưu việc sử dụng Pivot Table trong Excel. Điều đó giúp bạn có thể sử dụng Excel như 1 công cụ BI (Business Intelligence) hoàn chỉnh trong doanh nghiệp.

Hướng dẫn cách cài đặt Power Pivot trong thanh Menu trong Excel Hướng dẫn cách tải dữ liệu vào Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Excel Power Pivot

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

How To Combine Tables With Power Query

Bottom Line: Learn how to combine tables in Excel using Power Query.

Skill Level: Intermediate

Video Tutorial

Download the Excel File

If you’d like to download the file that I use in the video, you can do so here:

Here is the file that contains the VBA macro to create Power Query connections to all tables in the workbook.

Combining Tables

If you have tables on several worksheets that contain the same type of data and you are looking to combine them into one master table, Power Query can help you do it quickly and effectively. This is a great alternative to copying and pasting data piece by piece, which can get tedious if there are several tables that you want to merge.

There are just two prerequisites to keep in mind.

Prerequisite #1

All of the sheets or data sets that you are looking to combine must be formatted as Excel Tables, not just data set up in a table format.

To turn a data set into an Excel Table, just select any cell in the set and then choose Format as Table on the Home tab. It’s usually a good idea to name the table after you’ve created/inserted it.

If you are relatively new to Excel Tables, check out my Beginner’s Guide to Excel Tables. Here are some useful Tips & Shortcuts for Inserting Excel Tables, and this post will give you some Best Practices for Naming Excel Tables.

Prerequisite #2

The tables you are working with must contain the same column headings, though they do not have to be in the same order. If you are working with columns that have similar data, but your headings are not the same, Power Query will put them into different columns when it combines them.

There are ways around this, which I will cover in a future post.

The Setup Work in Power Query

Now you can create queries in Power Query. First we will create connection queries for each table. Then we will combine those queries with an Append query to combine or stack the data.

1. Create Connection Queries to the Tables

To combine, or append, your tables together, you need to create a connection to each of them in Power Query.

This brings up a preview of your data. To create a connection:

Select Close & Load To…

That brings up the Import Data window. From here, select Only Create Connection.

You can see the connection you’ve just created in the Queries & Connections pane. If ever you don’t see the Queries & Connections pane, you can open it by selecting that button on the Data tab in the ribbon.

This process of creating connections must be repeated for every table that you want to append. Again, you only need to do this work one time for the initial setup. However, here are a few tips to speed up the process.

Use the Table Connections Macro

Since creating and connecting lots of tables can be time-consuming, I’ve created a macro that automates it. The macro loops through all tables in the workbook and creates connection only queries for any table that do not have queries yet.

I will write a post in the future that explains the macro. However, you can download the file that contains the VBA macro code here.

The macros runs on the Active Workbook. You can add the macro to your Personal Macro Workbook and add a macro button to the Ribbon or Quick Access Toolbar to run it on any open workbook.

Close & Load Settings

If you’d rather not use a macro, you can also shorten the process by changing the setting of the Close & Load split-button. The default for the top half of that button will load the output table to a new sheet, but you can adjust the settings so that it only creates a connection instead. To change the setting:

Go to the File menu.

Select Options and Settings.

Choose Query Options.

That will bring up the Query Options window, where you can select Specify custom default load settings.

Deselect the Load to worksheet option.

Hit OK.

Just remember to change this setting back once you’ve finished connecting all of your tables.

2. Combining Connected Tables with Append

Once all of your tables are connected, it’s a piece of cake to consolidate them:

This brings up the Append window, where we can select Three or more tables. This allows us to move any or all of the tables that we’ve connected from our Available tables (on the left) to the list of Tables to append (on the right).

Once you hit OK, you will be taken back to the Power Query editor, where you can see a preview of the combined tables. You can make adjustments and transformations to the data before closing the editor and loading the data to a new worksheet.

Updating & Refreshing the Data

This means we have fully automated this process. You do NOT have repeat the steps above every time your data changes or you get new rows in your tables.

Adding New Tables

If you ever want to add new tables to the query (or exlcude existing ones) you can reopen the Append window by:

Opening the Query Settings pane if it’s not already visible (View tab, then Query Settings).

This opens the Append window, where you can add or delete tables.

The new columns will still need to have the same column header name on each sheet. If any of the tables are missing columns, then Power Query will fill the rows for that table with blank (null) values in the append query and output table.