Learn Advanced Excel – 3 Month Roadmap with Resources
Advanced Excel Skills are highly sought after. Use this FREE Excel roadmap to learn and grow you Excel skills in 3 months.
How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
Want to connect SharePoint files to Power Query but keep getting “unable to connect” error? Here is a simple fix to the problem.
FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]
It is the Soccer WORLD CUP time. Let’s celebrate the season with an AWESOME Excel File to track game schedules, results & scores.
Extract data from PDF to Excel – Step by Step Tutorial
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.
Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks
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
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.
Multiple Find Replace with Power Query List.Accumulate()
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.