Bạn đang xem bài viết The Complete Guide To 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.
What is power query?Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed.
It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA.
The best part about it, is you don’t need to learn or use any code to do any of it. The power query editor records all your transformations step by step and converts them into the M code for you, similar to how the Macro recorder with VBA.
If you want to edit or write your own M code, you certainly can, but you definitely don’t need to.
Get the data used in this post to follow along.
What Can Power Query Do?Every month you need to go to the folder where the file is uploaded and open the file and copy the contents into Excel.
You then use the text to column feature to split out the data into new columns.
The system only outputs the sales person’s ID, so you need to add a new column to the data and use a VLOOKUP to get the salesperson associated with each ID. Then you need to summarize the sales by salesperson and calculate the commission to pay out.
You also need to link the product ID to the product category but only the first 4 digits of the product code relate to the product category. You create another column using the LEFT function to get the first 4 digits of the product code, then use a VLOOKUP on this to get the product category. Now you can summarize the data by category.
Maybe it only takes an hour a month to do, but it’s pretty mindless work that’s not enjoyable and takes away from time you can actually spend analyzing the data and producing meaningful insight.
Where is Power Query?Power Query is available as an add-in to download and install for Excel 2010 and 2013 and will appear as a new tab in the ribbon labelled Power Query. In 2023 it was renamed to Get & Transform and appears in the Data tab without the need to install any add-in.
Importing Your Data with Power Query
Get data from a single file such as an Excel workbook, Text or CSV file, XML and JSON files. You can also import multiple files from within a given folder.
Get data from various databases such as SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata and SAP HANA databases.
Get data from Microsoft Azure
Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.
Get data from other sources like a table or range inside the current workbook, from the web, a Microsoft Query, Hadoop, OData feed, ODBC and OLEDB.
We can merge two queries together similar to joining two queries in SQL.
We can append a query to another query similar to a union of two queries in SQL.
Note: The available data connection options will depend on your version of Excel.
Depending on which type of data connection you choose, Excel will guide you through the connection set up and there might be several options to select during the process.
A Simple Example of Importing Data in an Excel FileThis will open a file picker menu where you can navigate to the file you want to import. Select the file and press the Import button.
When you’re satisfied that you’ve got everything you need from the workbook, you can either press the Edit or Load buttons. The edit button will take you to the query editor where you can transform your data before loading it. Pressing the load button will load the data into tables in new sheets in the workbook.
In this simple example, we will bypass the editor and go straight to loading the data into Excel. Press the small arrow next to the Load button to access the Load To options. This will give you a few more loading options.
The Query Editor
The Ribbon – The user interface for the editor is quite similar to Excel and uses a visual ribbon style command center. It organizes data transformation commands and other power query options into 5 main tabs.
Query List – This area lists all the queries in the current workbook. You can navigate to any query from this area to begin editing it.
Formula Bar – This is where you can see and edit the M code of the current transformation step. Each transformation you make on your data is recorded and appears as a step in the applied steps area.
Properties – This is where you can name your query. When you close and load the query to an Excel table, power query will create a table with the same name as its source query if the table name isn’t already taken. The query name is also how the M code will reference this query if we need to query it in another query.
Applied Steps – This area is a chronological list of all the transformation steps that have been applied to the data. You can move through the steps here and view the changes in the data preview area. You can also delete, modify or reorder any steps in the query here.
The Query ListThe Query List has other abilities other than just listing out all the current workbook’s queries.
When you do eventually exit the editor with the close and load button, changes in all the queries you edited will be saved.
Copy and Paste – Copy and paste a query to make another copy of it.
Delete – Delete the query. If you accidentally delete a query, there’s no undo button, but you can exit the query editor without saving via close and load to restore your query.
Rename – Rename your query. This is the same as renaming it from the properties section on the left hand side of the editor.
Duplicate – Make another copy of the query. This is the same as copy and paste but turns the process into one step.
Move To Group – Place your queries into a folder like structure to keep them organised when the list gets large.
Move Up and Move Down – Rearrange the order your queries appear in the list or within the folder groups to add to your organisational efforts. This can also be done by dragging and dropping the query to a new location.
Create Function – Turn your query into a query function. They allow you to pass a parameter to the query and return results based on the parameter passed.
Convert To Parameter – Allows you to convert parameters to queries or queries to parameters.
Properties – Allows you to change the query name, add a description text and enable Fast Data Load option for the query.
The Data PreviewThe main job of the data preview area is to apply transformation steps to your data and show a preview of these steps you’re applying.
You can choose from decimal numbers, currency, whole numbers, percentages, date and time, dates, times, timezone, duration, text, Boolean, and binary.
The Applied StepsYou can insert new steps into the query at any point by selecting the previous step and then creating the transformation in the data preview. Power Query will then ask if you want to insert this new step. Careful though, as this may break the following steps that refer to something you changed.
Edit Settings – This allows you to edit the settings of the step similar to using the gear icon on the right hand side of the step.
Rename – This allows you to rename the steps label. Instead of the displaying the generic name like “Filtered Rows“, you could have this display something like “Filtered Product Rows on Pens” so you can easily identify what the step is doing.
Delete – This deletes the current step similar to the X on the left hand side of the step.
Delete Until End – This allows you to delete the current step plus all steps up until the end. Since steps can depend on previous steps, deleting all steps after a step is a good way to avoid any errors.
Insert Step After – This allows you to insert a new step after the current step.
Move Up and Move Down – This allows you to rearrange the query steps similar to the dragging and dropping method.
Extract Previous – This can be a really useful option. It allows you to create a new copy of the query up to the selected step.
The Formula Bar The File Tab
Close & Load – This will save your queries and load your current query into an Excel table in the workbook.
Close & Load To – This will open the Import Data menu with various data loading options to choose from.
Discard & Close – This will discard any changes you made to the queries during your session in the editor and close the editor.
Note, you will still need to save the workbook in the regular way to keep any changes to queries if you close the workbook.
Data Loading OptionsYou can choose to load the query to a table, pivot table, pivot chart or only create a connection for the query. The connection only option will mean there is no data output to the workbook, but you can still use this query in other queries. This is a good option if the query is an intermediate step in a data transformation process.
You’ll also be able to select the location to load to in your workbook if you selected either a table, pivot table or pivot chart in the previous section. You can choose a cell in an existing worksheet or load it to a new sheet that Excel will create for you automatically.
The other option you get is the Add this data to the Data Model. This will allow you to use the data output in Power Pivot and use other Data Model functionality like building relationships between tables. The Data Model Excel’s new efficient way of storing and using large amounts of data.
The Queries & Connections Window
Data Preview – This is a live preview of the data similar to when first setting up a query.
Last Refreshed – This will tell you when the data was last refreshed.
Load Status – This displays whether the data is loaded to a table, pivot table, pivot chart or is a connection only.
Data Sources – This will show you the source of the data along with a count of the number of files if you’re it’s a from folder query.
The Home Tab
Close – You can access the Close & Load and Close & Load To options from here. These are also available in the File tab menu.
Manage Columns – You can navigate to specific columns and choose to keep or remove columns.
Reduce Rows – You can manage the rows of data from this section. There are lots of options to either keep certain rows or remove certain rows. Keep or remove the top N rows, the bottom N rows, a particular range of rows, alternating rows, duplicate rows or rows with errors. One option only available for removing rows is to remove blank rows.
Sort – You can sort any column in either ascending or descending order.
Transform – This section contains a mix of useful transformation options.
Split Columns – This allows you to split the data in a column based on a delimiter or character length.
Group By – This allows you to group and summarize your data similar to a Group By in SQL.
Data Type – This allows you to change the data type of any column.
Use First Row as Headers – This allows you to promote the first row of data to column headings or demote the column headings to a row of data.
Replace Values – This allows you to find and replace any value from a column.
Combine – This sections contains all the commands for joining your query to with other queries. You can merge, append queries or combine files when working with a from folder query.
Parameters – Power Query allows you to create parameters for your queries. For example when setting up a from folder query, you may want the folder path to be a parameter as so you can easily change the location. You can create and manage existing parameters from this section.
Data Sources – This section contains the data source settings including permissions management for any data sources that require passwords to access.
New Query – You can create new queries from new data sources or previously used data sources from this section.
The Difference Between the Transform and Add Column TabsThe bulk of all transformations available in power query can be accessed through either the Transform tab or the Add Column tab.
You might think there is a lot of duplication between these two tabs. For example, both tabs contain a From Text section with a lot of the same commands. It’s not really the case, there is a subtle difference!
When you use a command from the Add Column tab that is found in both tabs, it will create a new column with the transformed data and the original column will stay intact. Whereas using the equivalent command from the Transform tab will change the original column and no new column is created.
This is a critical point to be aware of!
The Transform Tab
Table – This section contains commands that will transform the entire table. You can group and aggregate your query, promote rows to headers, demote headers to rows, transpose your data, reverse row order, and count rows.
Any Column – This section contains commands that will work on any column of data regardless of data type. You can change the data type, automatically detect and change the data type, rename the column heading, find and replace values, fill values down (or up) a column to replace any blanks or nulls with the value above it (or below it), pivot or unpivot columns, move columns to a new location or convert a column to a list.
Text Column – This section contains commands for text data. You can split columns with a delimiter, format the case, trim and clean, merge two or more columns together, extract text, and parse XML or JSON objects.
Number Column – This section contains commands for numerical data. You can perform various aggregations like sums and averages, perform standard algebra operations or trigonometry and round numbers up or down.
Date & Time Column – This section contains commands for date and time data. You can extract information from your dates, times and duration data.
Structured Column – This section contains commands for working with nested data structures such as when your column contains tables.
The Add Column Tab
General – This section allows you to add new columns based on formulas or custom functions. You can also add index colummns or duplicate a column from here.
From Text – Very similar to the From Text section in the Transform tab, but these commands will create a new column with the transformation.
From Number – Very similar to the From Number section in the Transform tab, but these commands will create a new column with the transformation.
From Date & Time – Very similar to the From Date & Time section in the Transform tab, but these commands will create a new column with the transformation.
The View Tab
Layout – This section allows you to either show or hide the Query Setting pane (which contain the properties and applied steps) and the Formula Bar.
Data Preview – This section allows you to show or hide whitespace characters or turn the font into a monospace font in the data preview area. This is handy when dealing with data delimited by a certain number of characters.
Columns – This allows you to go to and select a certain column in the data preview. This command is also available in the Home tab.
Parameters – This allows you to enable parameterization in data sources and transformation steps.
Dependencies – This will open a diagram view of the query dependencies in the workbook.
In particular, the Query Dependencies view is a useful resource that allows you to see a visual representation of the data transformation process flow.
ConclusionsPower Query can seem overwhelming at first to someone new to it all, but the UI is very well laid out and easy to catch on to. While it might be new to a user, a lot of the concepts should be familiar to an Excel user already.
Getting familiar with all the parts of the editor and the layout of the ribbon tabs is an essential first step in exploring Power Query and incorporating it into your everyday work.
While there is a lot to learn about Power Query, it is worth putting in the time to learn. There is massive potential to save time in repetitive data cleaning and formatting tasks with it. It’s one of the most powerful and useful tools that has been added to Excel since pivot tables.
Want even more power query goodness? Then check out these Amazing Power Query Tips to help you get the most out of it!
Complete Guide To Team Building In Afk Arena (Updated)
The goal of this guide is to help you reflect on how to build a team, instead of giving pre-ordered compositions.
The examples given are mainly here to illustrate the points explained in General knowledge.
Why are you often suggested to play Shemira with 3 Wilders? What is the main objective and how can you adapt to it? In short, is there any magical solution to go through the content of the game like a breeze?
Like most of the presently available guides for AFK Arena, this guide is purely subjective and therefore not necessarily the absolute reality. Its aim is to help you better understand the game’s mechanics. Up to you on whether to apply my suggestions… or not!
Guide to Team Building: General KnowledgeBuilding a team is based around one carry. This carry represents your main DPS for your team, and your main objective is to build your team around that carry.
Play only one main carry, otherwise, your team may lack inefficiency at a certain point.
You will see on the below examples that positions are quickly taken by supports or tanks that will allow your main carry to do his job, it is then complicated to play with two carries.
How to build around the main carry?It’s basically comprehending the situation.
The most important thing is to see if your hero can be played as your main carry or not. Your main carry should be able to get rid of multiple enemies by himself if you give it a proper setup.
Once you have selected your main carry, begin analyzing their toolkit.
What is their weakness, which heroes can overcome it?
What could help them gain more power?
Is the hero useful since the start of the fight or does it depend on its ultimate?
Ask yourself as many questions as possible in order to assess their strong and weak points so that you can build your team accordingly around it.
Examples: The Most Used Carries in AFK ArenaIn order to put in use the above-mentioned theory in the guide, I will give you some examples on my way of thinking in how building teams that we often see around, thus being the most efficient ones.
The most used main carry, suggested by everyone due to it being so easy to loot and her huge capability of 1v5
Strengths
Overall ultimate on a decreased time
Life stealing immediately once reaching level 161
High critical strike stat
Higher HP amount than most mages, meaning she can be played on the front line
Once the ultimate spell is activated, she can 1v5 most of the time
Her toolkit is particularly useful when Shemira is at a higher level than your other heroes, which allows her to carry your team by herself.
Weaknesses
Highly dependent on her ultimate skill, as 80% of her efficiency is based on it
Easily targetable by crowd control and burst damage
No union in the library.
How to counter her WeaknessesAs a hero depending on her ultimate, Shemira needs a lot of time to activate it.
In order to find that time, Crowd Control is a must. And we will talk more about Crowd Control in this guide.
This is why Shemira is often seen along with Tasi and Nemora, who bring a huge life to sustain the team. Thanks to their crowd control skills and healings.
Arden as well is an interesting choice to play with Shemira, thanks to his triple root. Beware, though: His level being capped to 160, you have to be wise in his positioning in order to have him surviving as long as possible.
Lyca is often played alongside those heroes to bring some extra movement and attack speed and a bonus of energy at the beginning of the battle that allows your heroes to cast their crowd control spells before the enemies can cast theirs.
And lastly, Brutus is mainly played with this team. Thanks to his invulnerability that allows a short but efficient temporization on one side of the map.
Boosting her StrengthsYou can think about it the other way around.
Instead of letting Shemira slowly, but surely, gain in energy, we could play supports that boosts your team’s energy so that Shemira can cast her ultimate as quickly as possible.
For that purpose, we are going to look into the Lightbearers, particularly towards Rowan and Rosaline.
Those two heroes provide healing and control spells to your team like Tasi and Nemora.
But what differentiates them from their wilder counterparts is that they have their support skills based on energy gain, most especially Rowan with his Signature Gear that is considered one of the most powerful of the game (enabling heroes to ult upon reaching 600 points of energy instead of 1000).
We can also add Lucius as the main tank, who is dependent on his ultimate skill as well for both his and the team’s survival.
The last hero in this team will vary depending on the enemies you will be facing. You could opt for a Fawkes or Nara for an extra crowd control skill, and Estrilda for an additional energy gain, or finally a Thoran, Hendrick or Grezhul if the enemy possesses burst damage.
Compared to the Wilder team, this composition demands much more heroes to adapt against the many different enemy teams, as well as Rowan’s Signature Gear level at level 20 minimum to work efficiently.
Hence the fact that this team is much tougher to put in place.
Alternate possibility
Support: Nara, Arden, Ferael
Tank: Grezhul, Lucius, Thoran
(Beware, Shemira is not protected when Thoran is being resurrected and thus can easily be killed)
Note: Celestials and Hypogeens are not taken into account in order to maximize the accessibility.
Alternate possibility
Support: Nara, Ferael
Tank support: Estrilda
Tank: Grezhul, Thoran, Brutus, Hendrick
Comments for the Shemira GuideShemira is considered as the best character to carry your team until chapter 24 (which in general means that all your team has reached level 240).
Once you have reached this point, it becomes harder to play her at the front line, since she will hardly be able to tank enemies that are at a higher level than her, thus dramatically reducing her energy gain.
She will occasionally be played at this level, and other carries will be taking the priority, especially Ferael or even Eironn, that work perfectly well with the Wilder team composition.
Strongest damage dealer amongst the Wilders
Strengths
Hero mixing damage and crowd control
Huge clutch potential since he can dodge and freeze randomly
Possesses burst (instantaneous burst damage) but also DPS (damage on time)
Weaknesses
HP level relatively low
He is a melee hero even though being a DPS
His grab allows him to be played on the backline, but it can sometimes backfire on him and end up being more difficult to keep him alive
How to counter Eironn’s weaknessesLyca, Tasi and Nemora, as usual, bring a lot of sustainability to Eironn’s grab.
The movement speed gain that Lyca offers allows Eironn to have the initiative and to land the crowd control faster, whereas Nemora and Tasi do their usual work by providing healing and crowd control.
Adding to this, Ulmus and Seirus are good choices to be played in front of Eironn. Ulmus would indeed root the enemy in front of him that Eironn will be grabbing towards him when Seirus will land a bump on multiple enemies once they are grouped up.
Boosting his StrengthsLyca’s Signature, especially when level 20 or above, brings a decent damage upgrade to Eironn who already has a high accuracy level.
As usual, Lyca can hardly be separated from her fellow acolytes Tasi and Nemora.
Eironn can also be played alongside Safiya, his grab brings the enemies in Safiya casted zone and offers an extra AoE damage, thus bringing in huge synergy to this team.
Playing Eironn and Safiya puts in the team two carries at the same time, thus being sometimes situational, but the synergy is pretty powerful and can be considered as an exception.
As for the tank, many choices are open. Brutus is mostly chosen thanks to his passive skill (the roar) which nullifies enemies’ dodge and allows Eironn to successfully land the majority of his hits.
AFK Arena Example Guide for EironnTank: Ulmus, Seirus
Support: Safiya, Arden, Nara, Ferael
CommentsEironn is quite greedy when it comes to set him up properly :
A high Signature on himself
High-level Signature allies
High dodge level (mainly acquired through gears)
It also needs a high amount of Wilder fodder to Ascend the Wilder heroes’ team
Eironn will mostly be used as a transition for the late game, having similarities with Shemira’s team composition since they are on the same basis. You can focus his Signature to level 30, being one of the most useful heroes.
In order to help you in your choice of carry, here is a list of heroes that can be used as main carry (we are not talking about viability here, it is up to you to define what is within your reach):
Athalia – Celestians
Zolrath – Hypogeans
Belinda – Lightbringers
Gwyneth – Lightbringers
Thane – Lightbringers
Khasos – Maulers
Vurk – Maulers
Safiya – Maulers
Antandra – Maulers
Kaz – Wilders
Eironn – Wilders
Shemira – Graveborns
Isabella – Graveborns
Ferael – Graveborns
ConclusionThink carefully about your choices for ascension to build teams as you wish.
As you understood, there are many possible and playable options on AFK Arena, depending on your looting luck and your choice of gameplay.
These examples were merely here to highlight different possible combinations and why it would be viable, but by now you probably understood that there is no real answer to “Is there a God Tier comp I can focus ?”.
We hope we grant you the keys to help you build your dream combo.
Thank you for reading this Guide till the end, don’t hesitate to contact us to interact about it! Guide written by Vlab (ID : 607747 / Discord Tag : Vlab#4004) and Deimer (ID : 7948033 / Discord Tag : Deimer#0485) Special thanks to all community supporters for these feedbacks and to Fealynn (ID : 1106434 / Discord Tag : Fealynn#6700) for proofreading, correcting and adding details.
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 2023 trở về sau (2016, 2023, 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 đúngViệ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ệuKhi 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 chungTô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ụngNế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
How To Merge Tables With Power Query
Bottom Line: Learn how to merge tables or queries in Power Query to look up data and return matching results. This is similar to a Vlookup or Join where a relationship is created between two tables.
Skill Level: Intermediate
Video Tutorial Download the Excel FileYou can practice merging tables using the same Excel file that I use in the video. Download it here:
OverviewWe received a great question from a member of the Excel Campus community, Bill Evans, who wanted to know how to take data from two tables that are formatted differently and combine them into a single sheet using Power Query.
The answer involves using the Merge (or join) feature in Power Query. It basically creates a relationship between two tables to look up data and return matching results.
This is similar to what a VLOOKUP can accomplish with a formula. However, Power Query allows us to automate this entire process, along with any other data cleanup work, and is less prone to formula errors.
If you are looking to combine data by stacking tables together, that is called an Append. You can learn how to append tables in this post: How to Combine Tables with Power Query.
If all of this is sounding a little over your head because you are somewhat new to Power Query, take a break from this post and head over to my Power Query Overview. That will give you a better understanding of how and why it’s used. And this tutorial will walk you through installing Power Query.
Step 1: Create a Connection to the Lookup TableTo join two tables, we want to start by creating a connection-only query for the table that we will be looking up. Usually, when a query is run, it outputs the result in a new table in the workbook. But for this step, we just want to create the connection without creating a new output table. Here’s how:
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.
Step 2: Use the Merge Feature to Join the TablesOnce we’ve established a connection for the lookup table, we can merge it with the data from another table. This other table does not have to be in the same workbook. It could be from another workbook, a CSV file, a webpage, a database, or some other source.
In this example we will use a Table in Excel as the source.
To create a query for that source, start by going to the Data (or Power Query) tab and selecting From Table/Range.
On the Home tab of the Ribbon, select Merge Queries. This brings up the Merge window.
First, in the top part, you can select the column that you want to use for merging.
Then, in the middle, you select the table that you want to merge your data into.
Finally, in the lower section, you will choose the matching column. For my example the columns that we are using to merge both contain the customer ID numbers.
You can leave the Join Kind field as Left Outer. The Left Outer join will return all of the rows from the first table, and only the matching rows from the second table.
At the bottom of the window you’ll see the numbers of rows that were matched. In this case it says “The selection matches 221 of 306 rows from the first table.” This means that some rows from the orders table did not have a matching ID in the customers table. It’s ok for now and we’ll look at how to fix it below.
We can go ahead and press OK.
You may notice that some of the tables have rows that say “null” and when you close and load your query, those cells are blank. This is because when you merged the two tables, Power Query was unable to find some of the data in the source table.
You’ll notice that all of the new columns have headers that begin with the name of the table it came from. That can get a little annoying, so if you want to avoid that, just uncheck the box that says Use original column name as prefix.
Updating the DataIn order to fix the null entries, you can just add the appropriate rows to the lookup table, and then refresh the query.
Going forward, if you make any additions or deletions to the source table(s), a simple refresh of the query will instantly update the output table.
Free Training Webinar on the Power ToolsRight 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.
Using this process in this post, two tables that have different column headers are joined together. This is not a VLOOKUP, but it accomplishes the same thing as a VLOOKUP using Power Query instead.
With Power Query we are able to automate the entire data import and cleanup process, which can save you a ton of time and help reduce errors.
Cập nhật thông tin chi tiết về The Complete Guide To 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!