There is just one little problem with Power Query. I've just tried it on an Excel column with 7000 rows. Where there are blank rows in the column, the data that immediately follows the blank row is not changed. I also found some errors... Perhaps It's better to try it on a few rows at a time....
Yes, like any automation, or "AI" (not really, but low level), you have to pay attention to the exceptions.
.
One of the (many) great parts of PQ user interface is you can review the data and make corrections as you go. If you are using "create column by example, you can look for the problems and correct them as you find them. You can "save and Load" from PQ to "normal" Excel. And if you later discover an error, you can go back to the "Query" to correct the error and reload the data.
.
Yes, I'm too am still a novice at PowerQuery. It is immensely powerful. It can quickly perform simple tasks . More involved tasks take a little more time (especially to learn). For videos that are too fast, I've found that downloading them and replaying them or specific sections until I understand works well. Even if you don't download, YouTube (at least) allows you to select other playback speeds. During replays , I actually like to bump speed up to 125% or even 150%.
Click on the 'Gear", then select Playback speed and pick a speed.
Here are some links I've used to learn PQ (I do have many more). Scan through them to find specific ones that will help with your issues. If you have more questions, I'll try to help you with them.
.
General advice.
Break your problem into smaller steps you can handle, slowly build up to the larger desired result.
Always be ready to take a step back and try a different approach.
I've seen many creative solutions using these 2 concepts.
.
.
General PQ Introductory Info
! Microsoft Power Query for Excel Help (in wiki)
(Rohn007: This is a VERY good place to start learning about PowerQuery. Just keep digging in to the links)
https://support.office.com/en-us/article/microsoft-power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94
This is MS home page for PowerQuery help, with links to MANY detailed help pages
Power Query provides data discovery, data transformation and enrichment for the desktop to the cloud.
.
! The Formula Bar (in Power Query) 2021 02 26
https://radacad.com/power-bi-quick-tip-the-formula-bar-in-power-query
https://youtu.be/G-OHpN1vYLo 3min
Often you do the transformation in Power Query using the graphical interface, but having the formula bar visible, makes it much easier to understand or change the transformations.
. * Power Query transformations
. * Power Query Formula Bar
.
This webinar presents a radical (to me) concept. If you can learn it and become comfortable using it, you will be well on the way to being a PQ Guru.
.
@ “Are They Power Query Steps, or Are They Variables” 2021 02 18
https://www.youtube.com/watch?v=ZZS2Szc2Ues (85min)
e pq- Are They Power Query Steps or Are They Variables Presentation Files 2021-02-18.mp4 85min
e pq- Are They Power Query Steps or Are They Variables Presentation Files 2021-02-18.zip
https://answers.microsoft.com/en-us/msoffice/forum/all/powerquery-radical-new-concept-you-can-use-queries/62d98e76-ba7e-458b-8a5e-9b39cdfd968a
Gašper Kamenšek will lead us through a different approach to thinking about Power Query steps.
( I spend 3 or 4 hours replaying this webinar to create this timeline to use as a reference for future questions)
Timeline: 00:00 – Intro
05:15 – What’s New in PowerBI Feb 2021
08:43 – Step Folding indicators in PowerQuery Online
11:18 – Dynamic Clustering Filter (example for: https://feathersanalytics.com/filter-by-cluster-in-power-bi-part-1/ )
21:39 – Gasper: PowerQuery as Steps AKA Variables start / intro
23:45 – Define general concept of Variables using Demo file (downloadable): Demo 1 and 2 – Start.xlsx
26:00 – Demo using VBA Editor: Cell ColRow ref or named range, Let (), Lambda()
30:45 – Store value directly in “Source” step, act on stored value, each new step stores value
32:45 – retrieve value from sheet into blank Query using =Excel.Currentworkbook()
34:15 – Filter retrieved results, remove descriptions to leave values only
34:36 – Load to a new worksheet, create a data validation list to allow selecting of value (table names)
35:07 – Name the result “Selection”, return to PQ, copy first query, filter it to show only named range “Selection”, rename the copied query: **“**SelectedTable”
37:32 – Close and Load new query as a Connection Only
37:48 – create new query to load the source table (ACvPL)
38:11 – Edit the Source step to replace explicit name with value resulting from “SelectedTable” query, remove automatic generated “Changed Type” step from the query – now have a dynamic result table where you can pick the table name to display
39:20 – Recreate the result without creating duplicate query. The resulting query is pulling data from 2 sources
40:27 – Duplicate the ACvsPL query, Keep Top Rows (0) to keep only column headings, use header as first row (data), transpose to generate a list of column names, filter column names to only keep “Actual” values
43:04 – Convert these values into a data List: Transform tab > Any column group > Convert to List command. Rename the step that creates this list to “SelectedColumns”
43:44 – NOTE about step names with/without spaces: Step names with spaces need #” “ around the column name
44:54 – Use the generated list of column names, in step “SelectedColumns”, Create a new step that references original source step, remove some columns, then replace the generated list of remaining column names with stepname “SelectedColumns”
46:09 – Result, within a single query you use 2 variables: Source table and List (of column names) to generate a table with only dynamically selected column names.
47:58 – Most common use of the above technique, generate dynamic date table in PQ
48:23 – Demo: Technique to create Dynamic Date Table
48:23 – import the excel table with date into PQ, convert the data into PQ Date data type, change these dates into “Start of year” values: Transform tab > Date & Time column group > Date drop down > Year option > Start of Year option, remove duplicates: Right click on column heading > Remove Duplicates command
49:25 – duplicate the date column: Right click on column header > Duplicate Column Command, rename the new column ie Date2, change dates to end of year values: Transform tab > Date & Time column group > Date drop down > Year option > End of Year option, remove duplicates:
50:02 – Now use these dates by selecting Min value in Date column (ie Startdate) and Max value in Date2 (EndDate) column:
Select both columns, Convert dates to number data type: Home tab > Transform group > Data type dropdown > Number type
50:35 – Rename the “Changed Type1” step to “Base”.
51:09 – Retrieve the min value in Date column: Select column > Transform group > Statistics drop down > Minimum command. Rename the generated step “MinDate” (aka StartDate).
51:19 – create a new step referencing value in “Base” step: Click on fx in command line, change value to = Base. This retrieves values generated in Base step, so now select the Date2 column > Transform > Select Maximum. Rename the new step MaxDate (aka EndDate)
52:03 – create new step referring to values in MinDate and MaxDate steps: fx button > enter function = {MinDate..MaxDate} to generate the daily values from start to end date
52:17 – Transform the result into a table: Click on “x” button in command line > Name the table “Date”, Convert the values to Date data type. You now have date data type values from start to end date. You can regenerate this resulting date table by changing any dates in the source table and refreshing the generated date table
53:31 – Demo: Simulate “Fuzzy Match” in PQ: match to list of explicit values
55:30 – Describe Starting Point: User “submissions” reply to a question in free form sentence format. End result identify name of town found in the reply using match to lists of known “fuzzy” matches, ie proper name of city in different languages, or known “slang” terms for the city name or grammatical variations of the name, ie “Romans” fuzzy match to “Rome”
59:56 – Load Lookup table into PQ: create a new step referring to the raw source,
60:27 – Unpivot the lookup table remove the column containing the column names so you have base name and fuzzy match values, rename this step to “LookupTable”
60:50 – create new step to retrieve the “submissions” table data, rename the step “Submissions”.
You now have 2 variables, the lookup table and the submissions, all in the single Query
61:36 – Create columns to show if one of the lookup values was used: Add column tab > General group > Custom Column button. Define a function to match values from lookup table to text in submissions, one by one. The function will include user defined variable “LT”
65:05 – The new column contains a table. If you slide mouse pointer over the “Table” in each row, you will drill down into it to show the resulting values in a popup window in the bottom left corner of the PQ editor
65:33 – Click on filter drop down arrow in column heading, unselect “Value”, and uncheck “Use original column name as prefix”, and click OK to expand the results. You now have the submissions duplicated and each city name referenced in the submission
65:45 – Add new column with value = 1. Pivot on the city name column aggregating using sum function. You now have submissions on a single row, with the city names in separate columns with value of 1 or null. Close and load the result to a new worksheet
67:46 – Now you can change entries in submissions, adding new references to existing fuzzy matches, or you can add new entries to the Lookup table adding new fuzzy matches identified in the submission, both new city names and/or new fuzzy match values.
70:00 – Demo: Calculate percentage each value represents from year total
70:00 – Load the ACvsPL table into new Query, keep month and “Actual” column only, remove the rest
70:36 – Extract the Year from the column names, convert the text years into Number data type
70:57 – Generate percentage the value is for the entire year:
71:28 – Rename the “Changed Type1” step to Base
71:37 – Create a new step: Transform tab > Table group > Group By command: Group by Attribute, name the resulting new column “TotalYear”, use the “Sum” operation of Value. Rename the step “Totals”
72:08 – How to merge year Total values and values in Base: Create new step doing a TableNestedJoin() for Base and Totals, creating a new “Result” column using a “LeftOuter” join type.
73:38 – Expand the “table” in the result column to show the “Attribute” values only (that is the year totals). Add a new column with function using column value divide by column totalyear. You no longer need the “totalYear” column so you can remove it. Convert the calculated column to datatype Percentage
74:16 – Summary: instead of joining separate queries, you can perform all manipulations in a single query, just referring to the appropriate step names in a single query.
75:27 – Discussion on using explicitly defined names for Queries and Steps you build in documentation. Step names should not have spaces to make referring to them easier.
77:05 – Q&A
How to add comments to PQ Query steps? Right click on Step name > Select Properties. Add your comment to the “Description” area. OK to save change. When you hover mouse pointer over the step name the description/comment will display.
.
How to easily automate boring Excel tasks with Power Query! 2020 10 14
https://www.myonlinetraininghub.com/introduction-to-power-query
What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration.
If you haven’t heard of Excel’s Power Query tool, or you’ve heard of it but you’re not sure if it’ll be useful to you, then check out the video below where I showcase what the fuss is all about.
https://www.youtube.com/watch?v=L4BuUzccLpo&rel=0 17min
Power Query can automate the boring and laborious tasks of getting and cleaning data, reducing time spent on these tasks down to the click of a button!
00:29 How to get PowerQuery 2010-365, PowerBi
01:15 Why use PowerQuery (time saving)
02:10 Purpose of PowerQuery
02:35 Sources PowerQuery can get data from
03:23 Data Cleaning
03:43 Example 1: Get data from multiple files in a single folder - Intro
04:43 How to get data from a folder
05:50 Transform the data- intro to PQ user interface (starting with data in a single “sample” file)
06:48 Convert 2 row column headings into single row column headings
07:30 Split data in a column into multiple columns
08:28 Add a new column by multiplying 3 existing columns
09:25 Add a new column by example, removing honorifics from names
10:17 Add new column to calculate number of days from Order to Shipping
10:47 Filter data to remove some rows based on value(s)
11:34 Review recorded Query steps
11:45 Generalize the “sample file” query to apply to all of the files in the folder
12:10 Remove Source_name (file) column
12:18 Change column datatypes (not “formatting”) so Excel knows data types
13:22 Close and Load data directly to a PivotTable
14:22 Create a PivotTable
14:35 Auto Group Order Date
15:02 Create a Chart from the PivotTable
15:21 Get new data (new file) – Refresh All
.
! Power Query documentationhttps://docs.microsoft.com/en-us/power-query/
Power Query is the data connectivity and data preparation technology that enables end users to seamlessly import and reshape data from within a wide range of Microsoft products, including Excel, Power BI, Analysis Services, Common Data Service, and more.
! Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool 2020 05 13 Jon Acampora
https://www.excelcampus.com/power-tools/power-query-overview/
https://www.youtube.com/watch?v=sIejxpsbI3A&feature=emb_rel_pause (15min51)
Learn how this awesome feature of Excel and Power BI called Power Query will help you automate the process of importing, transforming, and cleansing your data to save a TON of time with your job.
. * The Power Query Data Machine
. * Common Data Tasks Made Easy
. * Overview of the Power Query Ribbon
. * Unpivot Data for Pivot Tables
. * Append (Combine) Tables with Power Query
. * Merge Tables – A VLOOKUP Alternative
. * Create Custom Functions
. * PQ Records Your Steps & Automates Processes
. * The Power Query Machine & Power BI
.
! Excel’s General problem that messes up what you type 2020 08 10
https://office-watch.com/2020/excels-general-problem-that-messes-up-what-you-type/
Why does Excel change what people type or import, sometimes in ways they don’t want? It’s the General cell format that messes with what you type or import.
Microsoft short explanation is ‘No specific format’ but that’s not really true. General is the ‘catch all’ cell type which will change to another cell format depending on what you type. Type $123 and the cell becomes Currency. Type 45% and it’ll be Percentage type.
That’s great mostly, but there are too many cases where General converts wrongly.
It doesn’t just happen with data import, typing data has the same problem. Trying typing ‘MARCH1’ into a cell, Excel will change it into a date. The ‘quick & dirty’ fix is to prefix the text with an apostrophe – typing ‘MARCH1 will force Excel to treat it as text.
You might think that Undo (Ctrl + Z) would fix a conversion from General but, for reasons unknown, the cell type conversions are not added to the Excel Undo stack. If they aren’t in the stack, they can’t be undone … Grrrrr.
.@ Data types vs formats 2017 10 11 Ken Puls
https://www.excelguru.ca/blog/2017/10/11/data-types-vs-formats/
One of the common questions I get in live courses, blog comments and forum posts is a variant of, “How do I format my data in Power Query or Power BI?” The short answer is that you don’t, but the longer answer is a discussion on data types vs formats.
. * What am I even talking about here?
. * Looking at the data in Power Query (in Excel or Power BI)
. * Data Types are not formatting
. * Data types vs formats
. * So how do we set formatting in the Query Editor?
. * Do I have to choose data types vs formats?
.
@ **** Robust Queries in Power BI and Power Query ****10 Common Mistakes You Do In #PowerBI #PowerQuery – And How To Avoid Pitfalls 2017 01 06 Gil Raviv
https://datachant.com/2017/01/06/10-mistakes-you-always-do-in-powerbi-powerquery/
The Challenge:
Data wrangling and cleansing is so easy with the Query Editor of Power BI and Excel (Power Query Add-In, or Get & Transform). The user interface is easy and rewarding, and it is even fun to use it. As you build your query using the UI, the Query Editor builds a series of formulas (AKA “M”, or Power Query Formula Language) which is based on the transformation steps you performed on a preview of the data. And this is an important thing to remember – The transformation is built on a preview of the data, and is heavily dependent on its format. When the real data starts deviating from the preview data, your queries may fail to refresh, or even worse – Incorrect transformation can lead to invalid data in your reports, which can eventually lead to wrong and dangerous business decisions.
. * What kind of changes in data will we address?
. * Which Data Sources will we address?
What kind of changes in data will we address?
We will focus on four common changes in the data:
. * Changes in column names
. * Changes in column types
. * New columns
. * New values in columns
. * Changes in nested field names in JSON/XML
.
@ Supercharge Excel with power tools – Financial Planning & Analysis
Pt1- Overall introduction to how the Power Tools work- Data cleaning 2016 11 17
https://www.accountingweb.co.uk/tech/excel/supercharge-excel-with-power-tools
Simon Hurst revs up his Excel Zone mini-series by using Power BI to tackle dodgy dates, numbers that don't add up and duplicates.
This is the first part of a 7 part series that will examine how these tools can replace a whole set of more traditional spreadsheet techniques.
.
Data Cleaning in General
Note: Oz du Soleil is a master at "data cleaning"!
@Example- **Data Clean Part 1 Different Ways to Format Data Using Power Query** 2017 01 10
https://ozdusoleil.com/2017/01/10/excel-power-query-data-cleansing-part-1-different-ways-to-format-data-using-power-query/
You will learn the different ways to format your messy data using Power Query.
Intro from John Michaloudis & Oz du Soleil
12:10 – Intro to Power Query (Get & Transform in Excel 2016)
16:30 – Trim leading & trailing spaces
20:30 – Format “text” Dates & Values using Excel v Power Query
24:15 – Parse URLs using Excel v Power Query
27:55 – Transform & automate reports from an ERP system (e.g. Oracle, SAP, QuickBooks) into a flat Excel file
.
**Part 2 Clean Extract Data Using Formulas Analytical Tools** 2017 01 10
https://ozdusoleil.com/2017/01/10/excel-power-query-data-cleansing-part-2-clean-extract-data-using-formulas-analytical-tools/
Date Specific Tips
@ Easily Fix Dates Formatted as Text with Power Query – Find/Replace text in PQ - Searching for Text Strings in Power Query 2020 10 21
https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query
https://www.youtube.com/watch?v=0RN3FZv3w84&rel=0 12min47
This week’s video from Mynda shows us how to easily fix dates formatted as text and addresses a common problem when opening CSV or text files in Excel containing dates that don’t match your region’s date format.
Power Query makes fixing dates entered as text in Excel super easy, and it's quick to update when you get new data.
. * The query to create a list of words (extract words from a table)
. * The list created by the query
. * Finding Substrings
. * Finds Substrings - Ignoring Case
. * Exact Match String Searches
. * Exact Match String Searches - Ignoring Case
. * PQ M Functions: List.ContainsAny(), List.Transform(), Table.AddColumn(), Table.ToList(), Text.Contains(),Text.Split()
.
4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings 2020 04 29 Jon Acampora
https://www.excelcampus.com/powerquery/power-query-date-errors-settings/
Learn 4 different ways to fix date data type errors in Power Query, including with locale, regional settings, and custom formulas with Column From Examples. Sometimes in Power Query, when you attempt to format data as a date, you will receive error messages. This is because Power Query is unable to recognize the data. The most common occurrence for this is when the original format of the date is from a different region.
. 1. Locale in Data Type Menu
. 2. Locale in Regional Settings
. 3. Operating System Regional Settings
. 4. Custom Formula with Column From Examples
.
Convert Text to Time Values with Power Query
https://www.excelcampus.com/powerquery/convert-text-to-time-values-power-query/
Learn how to use Power Query to convert times stored as text [## hours ## minutes ## seconds] to time values [h:mm:ss] that can be used for calculations and data analysis in Excel.
.
Convert UTC to Local time
Receive UTC time in text format: 2020-08-08T13:15:00-04:00
I want to apply the UTC time zone value to the date time.
First step in PowerQuery is to select the column
Right click, select change type
Should be Date/Time/Zone,
Use Power Query Convert Dates Stored as Text
https://www.youtube.com/watch?v=jtPL9pLgNsI (11min37)
Have you ever gotten a file and it there was a column that represented a date, but when you tried to perform some calculation with the value in the date column you get some error. It's most likely due to the fact that the values in that column are text representation of the date (i.e., Monday February 19 2018 6:28 PM). Excel will recognized this as text and one indication is to see if it is left aligned to the cell (values/numbers are right aligned to the cell). If you wanted to do date calculation you'd need to convert the date text into a "proper" date value. This video shows how to use Power Query to do that so check it out!
.
UTC- Handling Different Time Zones in Power BI / Power Query 2020-02-04 00:10:50**+00:00** 2019 10 21 Miguel Escobar
https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/
What time is it right now for you? We might share the same time zone, but that is usually not the case with worldwide operations.
If I say, let’s meet tomorrow at 8am. Will that be your 8am? Or will that be my 8am?
I feel like I should’ve posted this blog post a long time ago, but it’s better later than never. (maybe it was a time zone difference situaton? 🙂 )
In Power BI you can have date or date+time fields/columns once they’re loaded into your Data Model, but prior to loading them (inside the Power Query Editor) you can actually have them as date timezone, which is a specific data type that only holds date and time information, but also the time zone
.
Query by Example to Extract YYYY-MM from a Date Column
https://exceleratorbi.com.au/query-by-example-to-extract-yyyy-mm-from-a-date-column/
“Do you know of a way in power query to efficiently extract YYYY-MM from a Date column?” This can be done ‘manually’ with multiple steps. Or, if you know how to write M code, you could manually write a single line that will do the step for you. But I am a believer in using the UI to help you when ever possible. Let me show you how to do this using the Add Columns from Examples feature.
.
Create a new Column with current dateIt is possible to dynamically retrieve today’s date when authoring a Custom Column in the Query Editor. You can use DateTime.LocalNow() or DateTime.UtcNow() to get a date/time stamp, from which you can extract the date part.
This formula for the new column should work:
= Date.From(DateTime.LocalNow())
.
Extract Start and End Dates with Power Query 2019 11 29
https://www.myonlinetraininghub.com/extract-start-and-end-dates-with-power-query
Matt asked if we could extract start and end dates with Power Query. He has a list of non-contiguous dates and wants to identify the various date ranges. I’m going to cover two ways we can tackle this, one method requires few steps, but it may suffer performance issues on large tables, the other will be more efficient with bigger lists, but requires more steps.
.
Fill dates between dates with Power BI / Power Query 2019 07 23
https://www.poweredsolutions.co/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/
This is the post where I’ll show you exactly how you can use Power Query / Power BI to fill dates in the easiest fashion possible.
. * Case 1: Fill continuous Dates between dates
. * Case 2: Fill only x amount of days
. * Case 3: Fill specific day of the week between dates
. * Dealing with Date and Time
.
.
Always show Yesterday’s, Today’s or Tomorrow’s 2013 03 28
https://www.poweredsolutions.co/2013/03/28/cool-trick-always-show-yesterdays-todays-or-tomorrows-2/
Excel-guy: you need to check the date slicers to see what dates the report is usin
Executive: Ugh… I just want to click on the report and see the latest values
If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe (forecasting or that sort of scenario).
What could you do:
. * Teach the Executive to use slicers and how-to play with them (show him how fun that is!)
. * Drag the DATES to the rows or columns and use the dates filtering option
. * Create a DAX measure aka calculated field
. * Create a calculated column
The Solution:
. * Using the Dates as filters inside the pivot table
. * Using TODAY() and NOW() – volatile functions
.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
