Cập nhật nội dung chi tiết về The Complete Guide To Power Query mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác 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 2016 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 File
This 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 List
The 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 Preview
The 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 Steps
You 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 Options
You 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 Tabs
The 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.
Conclusions
Power 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!
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.
How To Install Power Query
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 2016.
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 2016 in order to use this feature as it’s not available for previous 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:
Go to the “File” tab.
Go to the “Help” section.
Here you will see the product version, if it says professional plus 2010, then you’re in luck.
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:
Go to the “File” tab.
Go to the “Account” section.
Here you will see the product version.
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 2016
Power query comes pre-installed in Excel 2016 but has been renamed to “Get & Transform” and is under the Data tab in the ribbon. If you have Excel 2016, then you don’t need to do anything to use it.
Download The Add-In
Go to the Microsoft website:
Select your preferred language.
Select The Correct Version
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.
English Words Used In French: Your Complete Guide To “Anglicismes”
Warning: This is not your parents’ French!
No, what we’re dealing with today is an altogether different, highly-evolved, complex creature.
Maybe your teachers haven’t told you, but recent French borrows lots of English words.
And this notable phenomenon isn’t just limited to tech words or online pop culture.
Hundreds, maybe thousands of English words and expressions, or anglicismes, have recently crept into the vast French lexicon.
Love it or hate it, learning modern French as it’s spoken involves appropriate use of the Frankenstein-esque hybrid derided as .
So unless you want to sound like a 19th-century professor, get comfortable and get ready to learn about the borrowed English words that power modern French speech.
English and French: A History of Exchange
If contemporary discussions on franglais generally refer to the introduction of English words in the 20th century, French and English have a significant history of interaction before that to consider, too.
English and French have been swapping words for a long time
When William the Conqueror invaded England, he installed his cronies as England’s new nobility. For centuries, you had a French-speaking elite being served by the indigenous English-speaking population. Hence, English items like “cow,” “sheep” and “pig” became “beef,” “mutton” and “pork” when served to their French overlords. Many such examples of two words meaning the same thing can be found in English. In fact, something like 45% of English words are borrowed from French. Talk about franglais!
French has experienced an “English onslaught” starting in the 20th century
There are several theories as to why anglicismes are entering French. In offices or other professional settings, for example, many such words are new concepts in French, like brainstorming or burn-out. They may be left as is since there’s no alternative. Plus, these words are often less cumbersome than proposed alternatives.
Similarly, in the tech field, or l’informatique, English words abound. In an Anglo-Saxon dominated industry, words like , cloud computing and open-source are adopted so quickly that language authorities barely have time to react.
Perhaps the legacy of the British Empire or current American cultural dominance has also given prestige to English among some French people, but that’s only speculation.
French Resistance to Anglicismes
A language that doesn’t evolve is a dead language, so adopting foreign words should be a sign of the French language’s vitality.
Given how much French has influenced English, you wouldn’t think that some words going the other way would be newsworthy. And it might be easier to accept if French were adopting an equal amount of words from several languages. What rubs some people the wrong way is that most new foreign words are of English origin, and this transfer is one-sided. Indeed, French language purists have not taken this sitting down.
Let’s take a look at some key issues, institutions and events in this debate.
Loi Toubon (Toubon Law)
Dubbing vs. subtitles
Bill 101
French in Quebec has been called the most regulated language in the world. The Quebecois have resisted assimilation in a sea of Anglophones for centuries. That they still exist as a viable Francophone community is amazing.
Bill 101, or the Charter of the French Language, makes French the official language in Quebec. It guarantees every Quebec citizen the right to receive government services in French, mandates business communication to the public in French and establishes the Quebec Office of the French Language and the Superior Council of the French Language, among other things.
By most metrics, the law has been successful. Francophones represent the vast majority of the population, and Quebec is one of the few places in the world where English is declining.
More controversially, Law 101 requires all immigrants, even Anglophones, to send their children to French-speaking schools, promotes francisation (think “Stop” signs becoming Arrêt, a step not taken in France) in the public sphere and imposes stiff penalties on businesses that don’t communicate adequately in French.
Académie française
Examples of Anglicisms and Proposed Alternatives
I could compose a dictionary of anglicismes, but certain words are used more commonly than others. Many are so widespread that they’ve entered into “correct” usage.
Anglicismes that have the same meaning in English
You won’t be misunderstood using certain common English words in French, such as:
As would be expected, many of these words deal with modern (Americanized) lifestyles. These words have not been adopted without confrontation, and if you feel uncomfortable using them, nobody will be offended if you use these alternatives:
Although some alternatives, like remue-méninges, are unwieldy, others have been adopted into common usage. If you’re learning Quebec French, for example, take note that email and week-end have been replaced with and fin de semaine.
This page provides an exhaustive list of anglicismes with proposed alternatives.
Anglicisms that have different meanings in English or don’t even exist in English
The French use certain anglicisms that either mean something totally different in English, or aren’t even actually words in English.
Some of my favorites are:
Others to look out for include:
Here is a very good list of “false” anglicisms to look out for.
How Should You Approach Anglicisms in Your Own French Speaking?
This is a touchy subject. Language teachers tend to avoid anglicisms, but in practice, that’s how French is spoken by many people, especially the young. Knowing the most popular anglicisms and when it’s appropriate to use them will make you a more nuanced French speaker.
Learn the “proper” language first
Teachers have a point. You’ll always have time to learn not only anglicisms, but how French is spoken colloquially. Start out with what you know is correct, and when you’re confident in this, you can move on to “street” French.
Know that in informal settings, English words are acceptable and common
Among friends, you’ll often hear very creative use of anglicismes and other forms of slang.
I have personally heard phrases like:
Je vais checker mon e-mail. (I’m going to check my email.)
Je l’ai liké sur Facebook. (I “liked” it on Facebook.)
Check out French Internet forums (and brush up your French on Facebook while you’re at it) for more. Many examples of franglais are pure works of art!
Avoid anglicisms in business settings, except for “common” English words
Obviously, you wouldn’t speak in formal or professional settings like you would to your friends. Especially in written correspondence, it’s best to avoid franglais, too. Certain words, however, like week-end and marketing, are accepted by the business community either because they don’t have equivalents in French or because they’ve entered into “adult” usage.
Languages evolve, and English has influenced French in a large way.
There are many sides to the issue, but franglais is real.
If you want to understand modern French, you’ll have to know how English words are used in the language.
Whether you’re a language purist or an SMS fanatic, it’s up to you to form your own opinion on and manner of dealing with English use in the French language.
And One More Thing…
To get familiar with anglicisms, slang and all the types of French used by natives today, try FluentU.
FluentU lets you learn French from real-world content like music videos, commercials, news broadcasts, cartoons and inspiring talks.
Since this video content is stuff that native French speakers actually watch on the regular, you’ll get the opportunity to learn real French-the way it’s spoken in modern life.
One quick look will give you an idea of the diverse content found on FluentU:
Start using FluentU on the website with your computer or tablet or, better yet, download the FluentU app from the iTunes store or Google Play store.
If you liked this post, something tells me that you’ll love FluentU, the best way to learn French with real-world videos.
Experience French immersion online!
Bạn đang đọc nội dung bài viết The Complete Guide To Power Query trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!