Skip to main content

Posts

Showing posts with the label Power Query

Split Column in Power Query

  Split Column In Power Query, a column of text can be split into multiple columns and in a number of different ways to achieve the results you want. By default, the name of the new columns contains the same name as the original column with a suffix of a period (.) and a number that represents each split section from the original column. You can then rename the column. Split Column Types - Click on the link below for details Split a column by delimiter Split a column by number of characters Split a column by positions Split a column by letter case combinations Split a column by digit and non-digit combinations

Power Query - Excel's most powerful tool

Power Query cleans and transforms data You add your data sources (Excel tables, CSV files, database tables, webpages, etc.) Press buttons in the Power Query Editor window to transform your data. Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting. Power Query is like a machine because once you have your query setup, the process can be repeated with the click of a button (refresh) every time your data changes. If you have used macros to transform your data, you can think of this as  a much easier alternative to VBA that does NOT require coding . Common Data Tasks Made Easy Do you work with data that has been exported from a system of record?  This could be a general ledger, accounting, ERP, CRM, Salesforce.com, or any reporting system that contains data. If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts. These data transformations could include ...

Power Query Basics - Clean Raw Data and reuse the file for updating new data

Download Example File from link below https://drive.google.com/file/d/1FnYGYGfTnFEeMwI-EZS89xgIOklrNBNA/view?usp=sharing Power Query is built in to Windows versions of Office 365, Excel 2016, Excel 2019 and is available as a free download in Windows versions of Excel 2010 and Excel 2013. The tool is designed to extract, transform, and load data into Excel from a variety of sources. The best part: Power Query remembers your steps and will play them back when you want to refresh the data. This means you can clean data in 80% of the normal time I say this about a lot of new Excel features, but this really is the best feature to hit Excel in 20 years. Get Power Query You may already have Power Query. It is in the Get & Transform group on the Data tab. But if you are in Excel 2010 or Excel 2013, go to the Internet and search for Download Power Query. Your Power Query commands will appear on a dedicated Power Query tab in the Ribbon. Clean Data the First Time in Power Query To give you a...