Imagine you get a sales report in a text file from your system on a monthly basis that looks like this.Įvery month you need to go to the folder where the file is uploaded and open the file and copy the contents into Excel. Get the data used in this post to follow along. If you want to edit or write your own M code, you certainly can, but you definitely don’t need to. 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. The best part about it, is you don’t need to learn or use any code to do any of it. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA. The user interface is intuitive and well laid out so it’s really easy to pick up. Power Query can import and clean millions of rows into the data model for analysis after. It allows you to set up a query once and then reuse it with a simple refresh. 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. You’ll learn about this later, in: Lesson 11‑31: Understand normal and de-normalized data. This enables you to combine relational data from disparate data sources to create a de-normalized data extract. The ability to combine data: Get & Transform enables you to merge queries.Better tools: Get & Transform has some advanced transformation tools that are not found in the standard Excel product (for example, the Unpivot Columns tool that you’ll learn about in: Lesson 11‑19: Unpivot aggregated data).You’ll learn more about pre-aggregating data later, in: Lesson 11‑18: Create an aggregated data query. The ability to transform big data: Get & Transform does not share Excel’s limits (of approximately a million rows of data) so it can be used to transform big data (sending the result directly to the data model, 3D Map, or to an Excel worksheet after aggregation).You’ll learn how to automatically refresh data later, in: Lesson 11‑3: Understand queries and connections. Automatically refreshed data: You can configure a query to automatically refresh an Excel table at a timed interval.You’ll learn more about queries later, in: Lesson 11‑3: Understand queries and connections. This means that all of the Get & Transform actions that you define can be repeated to refresh the data in a table on your worksheet with a single click. Re-usable and sharable queries: The Get & Transform tool generates a re-usable Query.
0 kommentar(er)
