All articles in 'Power Query' Category
![SQL vs. Power Query – The Ultimate Comparison](https://chandoo.org/wp/wp-content/uploads/2023/10/sql-vs-powerquery-sqr.jpg)
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
On the left side, we have a veteran warrior with 37 years of data battle scars and redundant six pack. They call him SQL.
On the right side, there is a young challenger with transformative powers and “never say undo” attitude. He goes by the moniker Power Query.
Who is going to win this battle?!?
Continue »![How to make a pivot table when you have data in multiple sheets [Tutorial]](https://chandoo.org/wp/wp-content/uploads/2023/09/SNAG-2968.png)
Recently I had to create a Pivot report from monthly data. But there is a twist. The data is spread across multiple sheets, one for each month. Let me explain how I built the pivot for that scenario.
Continue »How to “auto” generate calendar tables with Power Query – The best method
![How to “auto” generate calendar tables with Power Query – The best method](https://chandoo.org/wp/wp-content/uploads/2023/08/CALENDAR-TABLE-POWERBI.jpg)
Calendar (or date) table is crucial for performing date intelligence calculations in Power BI.
Normally, you would find a reasonable calendar table in most data models. But occasionally I come across models where there is no calendar table.
So I present to you, the ultimate & best way to generate calendar table using Power Query.
Continue »How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
![How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)](https://chandoo.org/wp/wp-content/uploads/2022/11/SNAG-2450.png)
Want to connect SharePoint files to Power Query but keep getting “unable to connect” error? Here is a simple fix to the problem.
Continue »Extract data from PDF to Excel – Step by Step Tutorial
![Extract data from PDF to Excel – Step by Step Tutorial](https://chandoo.org/wp/wp-content/uploads/2021/04/get-data-from-pdf-to-excel.jpg)
Recently I had to extract data from multiple credit card statements and combine them to one Excel table. In this tutorial, let me share you simple steps to deal with PDF data and extracting it to Excel.
Continue »Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks
![Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks](https://chandoo.org/wp/wp-content/uploads/2020/08/power-query-tutorial-and-examples.jpg)
Power Query (Get & Transform data in Excel) is a true game changer ?. It can simplify and automate various data activities. In this in-depth blog tutorial, learn all about this powerful time-saving technology. You will learn:
* What is Power Query and how to launch it from Excel or Power BI?
* How to connect and load data from any type of sources – excel files, CSVs, text files, web pages, PDF, folders etc.
* How to perform common data cleansing activities with Power Query
* Adding columns using rules
* Automating data collection, clean-up and publishing processes with Power Query
* Web scraping with Power Query
* SQL style Joins, merges, appends and unions with Power Query
* Consolidating data in a folder
* Data load, save & publish process
* Tips & tricks on using Power Query efficiently
How to export YouTube video comments to Excel file? – Free template + Power Query case study
![How to export YouTube video comments to Excel file? – Free template + Power Query case study](https://chandoo.org/wp/wp-content/uploads/2020/05/export-youtube-comments-to-excel.png)
This week, I am running a contest on YouTube. One of the criteria for picking winners is that they must comment on my video. So far, I got more than 200 comments. To make my job easier, I want to export the video comments to an Excel file. Turns out this is easily done once you have a Google developer API key. In this article, let me explain the process for extracting Youtube video comments to Excel table.
Continue »Multiple Find Replace with Power Query List.Accumulate()
![Multiple Find Replace with Power Query List.Accumulate()](https://chandoo.org/wp/wp-content/uploads/2020/05/multiple-find-replace-text-values.jpg)
Imagine you have a paragraph of text and you want to replace all occurrences of {four, normal, mysterious, nonsense} with {six, casual, confounding, handbags}. How would you do that?
You could use SUBSTITUTE() formula, but you need to nest four of them (as we need to replace four values with another four). But what if you have larger set of find / replacements?
Worry not, you can use Power Query to transform original text to new one by replacing all matching values.
In this page, learn how to do that with the excellent List.Accumulate() Power Query function.
Continue »Advanced Pivot Table Tricks for you
![Advanced Pivot Table Tricks for you](https://chandoo.org/wp/wp-content/uploads/2020/02/advanced-pivot-table-tricks.png)
Excel Pivot tables make data analysis and visualization easy. With the help of these advanced pivot table skills, you can create powerful data analytics and reports. New to Pivot Tables? If you are new to Pivot Tables, check out this excellent introduction to Pivot Tables page. × Dismiss alert Table of Contents #1 – One Slicer, Two […]
Continue »![How can you analyze 1mn+ rows data – Excel Interview Question – 02](https://chandoo.org/wp/wp-content/uploads/2019/09/how-to-handle-more-than-one-million-rows-in-excel.png)
As part of our Excel Interview Questions series, today let’s look at another interesting challenge. How can you analyze more than 1 million rows data in Excel?
You may know that Excel has a physical limit of 1 million rows (well, its 1,048,576 rows). But that doesn’t mean you can’t analyze more than a million rows in Excel.
The trick is to use Data Model.
Continue »![Yummy WAFFLE chart in Power BI](https://chandoo.org/wp/wp-content/uploads/2019/09/waffle-chart-power-bi-demo.gif)
Waffles are yummy, Power BI is awesome. The combination is going to send your taste-buds on a cruise. Learn how to make a yummy waffle chart in Power BI.
Continue »Job Title Matching Problem [Excel Homework]
![Job Title Matching Problem [Excel Homework]](https://chandoo.org/wp/wp-content/uploads/2019/08/can-you-match-the-job-titles-in-excel.png)
Howdy folks. Almost the end of August here. Let’s wrap it up with a nice little challenge, inspired from my recent consulting gig. Say you are looking at few job titles that look similar and want to match them to correct title.
Continue »How to extract common values in two tables? – Power Query Tip
![How to extract common values in two tables? – Power Query Tip](https://chandoo.org/wp/wp-content/uploads/2019/08/extract-common-values-from-two-tables.png)
We, humans like to compare. Whether we are on Facebook or workbook, we want to compare. So how do you compare two tables and extract common values? Simple, use Excel Power Query. It can merge (a la join) tables and give you the common values.
Continue »Combine multiple Excel files using Power Query [Full example + download]
![Combine multiple Excel files using Power Query [Full example + download]](https://chandoo.org/wp/wp-content/uploads/2019/05/power-query-man.png)
Say you want to combine multiple Excel files, but there is a twist. Each file has few tabs (worksheets) and you want to combine like for like, ie , all Sheet1s to one dataset, all Sheet2s to another dataset…
To make matters interesting each sheet has a different format.
What now?
Of course Power Query to the rescue.
Continue »How many calls we got outside office hours? [Excel / Power Query homework]
![How many calls we got outside office hours? [Excel / Power Query homework]](https://chandoo.org/wp/wp-content/uploads/2019/05/calls-outside-office-hours.png)
Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.
Continue »