Bạn đang xem bài viết Conditional Merge In Power Query đượ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.
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!
Power Query Là Gì? Tại Sao Cần Học Power Query?
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 2016 trở về sau (2016, 2019, 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
The Complete Guide To Installing Power Query
Power Query is an amazing data tool for Excel! It is built by Microsoft to help you import, transform, and automate your data processes. Checkout my overview of Power Query to learn more about it.
Here is a quick list of compatible versions:
“Free” add-in for Excel 2010 and 2013 for Windows.
Power Query is not yet available for the Mac versions of Excel.
Video Guide to Installing Power Query
What Version Do I Need?
The download page for Power Query lists which version of Excel are compatible with Power Query.
The following Office versions for Windows are supported:
Microsoft Office 2016 – All Versions
Microsoft Office 2013 – All Versions
Microsoft Office 2010 Professional Plus with Software Assurance
Both 32-bit and 64-bit are supported. Power Query is not compatible with any Mac versions of Excel yet.
Unfortunately, if you don’t have these versions you will need to upgrade to use Power Query. To me, Power Query is worth the upgrade to Office 365 alone, and there are a lot of other cool new features too.
How Do I Check My Version of Excel?
Excel 2010
Here’s how to determine your version and 32 or 64 bit in Excel 2010:
The Excel version will be listed under the Product Activated section.
The bit version (32-bit or 64-bit) will be listed under the About Microsoft Excel section.
Excel 2013
Here’s how to determine your version and 32 or 64 bit in Excel 2013:
The Excel version will be listed under the Subscription Product section.
Excel 2016
Power Query is included with Excel 2016 (Office 365). It has been renamed and is now on the Data tab of the Ribbon in the Get & Transform section.
Power Query is available with all levels of Office 365 subscriptions. Here is an article that explains the features available for each subscription level.
It’s important to note that there are some differences in Power Query for different builds of Excel 2016.
If you are on the MSI (Windows Installer) version then you will not get frequent updates to Power Query. Your ribbon might look different from mine, and you won’t have all the latest features.
If you are on the ProPlus version of Office 365 then you might be on the Deferred Channel. The name of the Deferred Channel has been changed to Semi-Annual Channel as of September 2017. This means you get updates every six months instead of every month.
Here is an article about the different channels for Office 365 ProPlus.
Here is an article on how to switch channels.
All the versions and channels make this very confusing, to say the least. Hopefully this guide helps you figure out how to get Power Query for Excel 2016.
Checkout my article on an Overview of Power Query to learn more about this awesome tool!
Excel 2010 & 2013
For Excel 2010 and 2013 you will need to download the Power Query add-in and install it.
The installation steps are about the same in Excel 2010 and 2013.
Close (exit) Excel completely.
My Power Query Tab Disappeared
If your Power Query tab ever goes missing, you can usually re-enable the add-in by going to the COM Add-ins menu.
There are a few ways to get to the COM Add-ins menu.
File menu.
Select COM Add-ins from the Manage drop-down.
There is also a button on the Developer tab that will take you directly to this window.
Power Query is my favorite tool for working with data in Excel. If you are not familiar with what Power Query can do, checkout my article on an Overview of Power Query to learn more.
Power Pivot And Power Bi: The Excel User’S Guide To Dax, Power Query, Power Bi &Amp; Power Pivot In Excel 2010
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 dives 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 presented 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 2016 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 entrepreneur, 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.
Cập nhật thông tin chi tiết về Conditional Merge In Power Query trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!