How to Easily Create a Dynamic Filled Map Chart in Excel with Power Query

Рет қаралды 101,605

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this video I'll show you how you can use Excel Power Query to import data from a CSV file to Excel. We'll use Power Query's data transformation features to extract the required information from a column. Then we'll use the Group By feature to summarize the data by state.
⬇️ Get the CSV file here: pages.xelplus.com/filled-map-...
Here's what you'll master:
- Quick CSV File Handling: Discover how to work with a big CSV file in Excel and create a direct connection for seamless data management.
- Data Splitting Made Simple: Learn to split city and state information in Excel using Power Query, even when they are in the same column.
- Effortless Data Summarization: See how to group sales by state in an Excel table effortlessly, just like making a pivot table but more straightforward.
- Creating a Filled Map Chart: Transform your summarized data into a visually striking filled map chart, ideal for presentations or reports.
- Dynamic Data Refreshing: Find out how to maintain a live connection between your table and the CSV file. This means any new data or states added will be updated with a simple refresh.
- Customization Tips: Personalize your filled map chart with different color schemes, map projections, and data labels to make your report stand out.
As a last step I'll show you how easy it is to create a Filled Map Chart based on this data set (available in Excel 2019 and Microsoft 365) in Excel.
LINK to Power Query playlist: • Excel Power Query (Get...
★ My Online Excel Courses ► www.xelplus.com/courses/
00:00 Import CSV file with Excel Power Query
02:03 Extract Text Between Delimiters with Power Query
02:51 Summarize Data with Group By in Power Query
04:04 Create Filled Map Chart in Excel
06:36 Refresh Query with New Data
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #powerquery

Пікірлер: 156

@LeilaGharani 4 жыл бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/filled-map-file Get access to the complete Excel Power Query course here: www.xelplus.com/course/excel-power-query/

@saids.4307 4 жыл бұрын

I bought your course already, Power Query & Power Pivot made me the undisputed data king in my company 💯✌🏾🤓 Only 32-bit is keeping me from flying 🦸‍♂️

@drjeckyll5305 4 жыл бұрын

Bought the course and highly recommend. Helped persuade my company to move from Office 2013 to finally move to 365. Thanks!!

@moidqureshi3075 4 жыл бұрын

Signed up yesterday on Udemy. Please make a similarly comprehensive, Beg. to Adv. course for Power BI as well.

@xalidmlikov7322 4 жыл бұрын

How to import live data excel to spreadsheet? Please help me. My e-mail adress is melikov_xalid@yahoo.com

@angiesmith2027 3 жыл бұрын

Is it possible to have more than one column of data labels? For example, in my second column I am going to use the data to color the states, but in my third column I want to display a different data point or name. In other words, say CA = Small for column 2, and column 3 = 2, so I want the color range based on column 2 (small, med, large, XL), and column 3 is the QTY, so CA is Small with 100 as a label, and WA is say Large with 200 as label, and OR is Med with 50 as a label. How can I do that in excel?

@onwedavid9124 2 жыл бұрын

Well well well, you're the best excel tutor out there.

@farzanmoha8935 4 жыл бұрын

I recommend everyone register in Leila’s' super useful power query course. Thank you Leila, I really really enjoyed the course and I know you put a lot of effort into covering every little trick.

@nevillegalappaththi7483 4 жыл бұрын

a reliable and knowledgeable tutor. I always follow her posts and save them for later reference.Thank you daughter.

@fergaltierney8672 4 жыл бұрын

It's Thursday, it's time for another great video from Leila! This Power Query tool will change my life...

@LeilaGharani 4 жыл бұрын

Great to hear! 😘

@KhoudorJichi 2 ай бұрын

the best excel tutor love all your vidoes

@SolutionsAbroad 4 жыл бұрын

I just found you on KZfaq and the quality of your KZfaq videos are stunning! Keep it up!

@LeilaGharani 4 жыл бұрын

Thank you! Will do!

@wayneedmondson1065 4 жыл бұрын

Hi Leila.. thanks for this. I have an immediate use and your tutorial put me right on track to implement it. Quick tip.. if you want to have control over the format of the label options (size, bold, font, etc.), then instead of showing map labels in series options, use data labels and define both Category Name and Value with the Separator as New Line. This then allows you to manipulate the format of that pair of data items (not possible when using map labels in series options). Great tutorial, tips and tricks.. always found at your channel and web site. Thanks for the video. Thumbs up!!

@LeilaGharani 4 жыл бұрын

Thanks for sharing these tips, Wayne!

@geminitwin 4 жыл бұрын

Thank you so much Ma'am,🙂 you've made my life easier by understanding how to work smart on MS Excel👍🏼

@LeilaGharani 4 жыл бұрын

Most welcome 😊

@kilviacosta 4 жыл бұрын

your videos are fantastic. His teaching didactics are simple and objective. congratulations

@mcd22630 4 жыл бұрын

You packed an incredible amount of new information in an 8 minute video. So I think the time stamps are well worth the effort. On another note, that was beautiful picture of Vienna you took from the Aurora restaurant. Hope you shared the experience with someone special.

@LeilaGharani 4 жыл бұрын

Glad you like it. Definitely was a nice evening 😊

@nadeemmalik8031 4 жыл бұрын

The Queen of Excel is back looking amazing as ever and with info at her fingertips. Brilliant example very well explained. Thank you.

@LeilaGharani 4 жыл бұрын

😘

@dbc123cbd 4 жыл бұрын

Hey Leila. I wonder if you can help me. 7am to 7pm is considered standard hours (4 hours is considered 1 session) and 7pm to 7am is considered premium hours (3 hours is 1 session) After Friday 5pm upto Monday 09:00 am is premium hours (3 hrs is one session) Week-1 Mon - 08:00 to 18:00 Tue - 15:00 to 22:00 Wed - 21:00 to 09:00 Thu - off Friday 09:00 to 15:00 Sat off Sunday 08:00 to 14:00 How to put a calculator in excel to work out number of sessions week-1 ? I am struggling to put the calculator together for this work pattern. Many thanks in advance

@rubenrositto6789 4 жыл бұрын

Impeccable as usual! I am doing your PQ course and it is the best I have seen. Although I am an advanced user in excel and PQ, I always learn something with you! Thank you very much for sharing and I hope you continue like this.

@LeilaGharani 4 жыл бұрын

Wow, thanks! I'm glad you like the course!

@bonneanneegeorge8976 4 жыл бұрын

Leila Gharani how can I contact you ? There’s something I would like to ask you about Excel

@saids.4307 4 жыл бұрын

TRUE 💯

@ExactProBi 4 жыл бұрын

Thanks, awesome tutorial as always, I have enrolled into the course!

@LeilaGharani 4 жыл бұрын

Cool, thanks! Hope you'll enjoy it!

@BenA-bu1cz 4 жыл бұрын

Thank you for sharing with iNet community.👍

@KP-ky1sn 4 жыл бұрын

Easy as 1 2 3 .....thanks again. very much appreciated. Excel is such a powerful program when applied for the Applications.

@LeilaGharani 4 жыл бұрын

You're very welcome, Peter!

@alix743 4 жыл бұрын

*Great Lessons on every Great Thursday!*

@LeilaGharani 4 жыл бұрын

😘

@vijayarjunwadkar 4 жыл бұрын

If it is Leila teaching you, it has to be the best! So here she comes again with another great video! Keep them coming... :-)

@LeilaGharani 4 жыл бұрын

More to come :)

@IvanCortinas_ES 4 жыл бұрын

Excellent explanation Leila. Thank you very much for sharing your knowledge.

@LeilaGharani 4 жыл бұрын

My pleasure, Iván. Hope it will come in handy for you.

@mattschoular8844 4 жыл бұрын

PQ makes it so easy.. Thanks Leila

@LeilaGharani 4 жыл бұрын

My pleasure, Matt!

@shakiraasfoor7599 4 жыл бұрын

Thank you leila Well Done

@Saddam-Abdulameer 4 жыл бұрын

Me too

@samuelkodjoe1645 4 жыл бұрын

i love what u did!!

@chrism9037 4 жыл бұрын

Thank you Leila, PQ to the rescue again!

@2_Bike_is_Life 4 жыл бұрын

Your boss needs it right now - What are you going to do? (Hire Leila) :D

@LeilaGharani 4 жыл бұрын

😁

@satishgopal2406 4 жыл бұрын

As usual... Best in Sharing Knowledge....... Thank you Leila!!

@LeilaGharani 4 жыл бұрын

My pleasure!

@HammamDisini 4 жыл бұрын

Thank you, great tutorial

@saids.4307 4 жыл бұрын

Just imagine 🤯 most Excel users would use some of your Excel powers 💪 I have to play with the map features 🙋‍♂️❗ I bought your PowerQuery course, I have only tak3n a small look yet, but shall I call it M agnificent 🤓

@LeilaGharani 4 жыл бұрын

🤗 Hope you'll enjoy the rest of the course!

@shabbirkanchwala-abwaab6263 4 жыл бұрын

Fan of LG with PQ Always Explaining PQ in Supey easy way Thnx

@LeilaGharani 4 жыл бұрын

So nice of you

@brighnquisitive6217 4 жыл бұрын

Another win, thank you ma'am

@darrylmorgan 4 жыл бұрын

Hi Leila!Great Tutorial,Power Query Makes Even The Most Disorganised Data Really Easy To Work With..Thank You :)

@LeilaGharani 4 жыл бұрын

It sure does Darryl :)

@itsqueenshepherd4762 4 жыл бұрын

Thank you. It really works🤩

@daseaton 3 жыл бұрын

Great presentation as always! I have a challenge for you that I am sure others would benefit from as well. My company has specific states in regions (everyone's regions will be different obviously). I am attempting to set up a dashboard that updates a map that shows the specific states in a region. Example: Northeast includes the states Maine, New Hampshire, Vermont, New York, New Jersey. I want to have a slicer button that reads Northeast that automatically displays a map of just those states. I have made several attempts without success. Thank you in advance!

@terrymakoilan1015 4 жыл бұрын

Very helpful... thanks!

@MyAudioBookCompilation 4 жыл бұрын

Thank you so much for this informations. 😁😁

@eagillum 2 жыл бұрын

OK 2:50 is mind-blowing. Group by in Power Query. "Like a pivot table in Power Query."

@rotethongvanit 2 жыл бұрын

Great job!

@hazemali382 3 жыл бұрын

Simple and great Leila ♥

@milindshiralkar6284 4 жыл бұрын

Nice tutorial 👌

@Inquire98 4 жыл бұрын

That was REALLY good 👌

@syedali3831 4 жыл бұрын

Many thanks!!!

@gvitullib 3 жыл бұрын

Excelent video!. Only one note: if you open the csv with excel for edit, then the file will change and the refresh will not work (at least in my case). I didn´t realize this step and spent almost one hour to figure out what was the problem, because "New Mexico" didn´t show in the table/map. Finally, I opened and edited the csv with notepad and worked fine.

@lucyene5060 4 жыл бұрын

Wow i loved it!!!!

@discovermore1 4 жыл бұрын

Very useful information

@ExcelAZ 4 жыл бұрын

Wonderful explanation 👏

@LeilaGharani 4 жыл бұрын

Thank you 🙂

@AdvanceOffice 4 жыл бұрын

Very informative mam :)

@johnborg5419 4 жыл бұрын

Amazing Leila. Thanks. : ) : )

@DDeniz-pq5sy 4 жыл бұрын

Impressed and inspired

@LeilaGharani 4 жыл бұрын

Glad to hear that :)

@luvlycan 4 жыл бұрын

Hello Leila... Hope you are doing good. I've watched so many videos of yours and they all pack with so much information. As I'm working in MIS and VBA role, many times I see Job openings for 'Profit forecastors'. Could you please make a video on this topic that what actually is Profit Forecasting. And will it be any good for a MIS and VBA person. Please, it's a request.

@omg8038 2 жыл бұрын

Thank you for your hard work. Ma'am, can you tell how to use Map Chart for smaller areas e.g. areas in a state/province.

@Mark-ft8yx 4 жыл бұрын

Very nice. Can this be done for every country individually? And can i do other areas such as states, regions, suburbs? Thanks. Much appreciated

@thenedak 4 жыл бұрын

Nice video i learn new things i want ask i can use the same idea on custom map i created

@mustaphaibraheem9475 4 жыл бұрын

Thanks wonder woman

@R808-n6k 4 жыл бұрын

The Queen strikes back with a map chart and power query!

@LeilaGharani 4 жыл бұрын

😘

@shoreshsoltanian878 4 жыл бұрын

Leila Gharani Hi Leila, hope you are well and safe. Will you provide the course for power BI? Last question Are you kurdish? Thanks

@Mamuchonga01 4 жыл бұрын

You are the best

@LeilaGharani 4 жыл бұрын

😘

@findthetruth3021 4 жыл бұрын

You are amazing wow.

@luapnagle 4 жыл бұрын

Nice !

@alvarorodriguezlasso 4 жыл бұрын

Amazing Excel, you are so pretty. Regards from Cali-Colombia.

@LeilaGharani 4 жыл бұрын

😘

@excelisfun 4 жыл бұрын

PQ Map-erific, Teammate : )

@saids.4307 4 жыл бұрын

I learned so much from YOU too 🕴 YOU MVPs should pool even more ➡ the data processing future is coming VERY SOON 🔜 🍀

@LeilaGharani 4 жыл бұрын

Thank you teammate Mike :)

@hazemali382 3 жыл бұрын

I learned so much from YOU Mr. Mike ;)

@eslamfahmy87 Жыл бұрын

Perfect

@hosseinhosseinpoor4845 3 жыл бұрын

thanks

@divyangvasavada3965 4 жыл бұрын

Good Tool

@vijayr9262 4 жыл бұрын

Hi Leila, Thanks for your videos! All nice and useful!! I have a case that I have received the Cheque from one of my Customer but really do not know from whom it’s.. How to validate it with my available data. Maybe the Chq. amount for multiple invoices per customer record. Do we have any functions or VBA codes for this. It’s like using sumifs and goalseek function. Pls. do the needful. Thanks in advance!

@anandseelan633 3 жыл бұрын

I LOVE U so much , Keep Rocking 👌👍👍👍👍

@LeilaGharani 3 жыл бұрын

Thank you so much 😀

@odelakumar06 2 жыл бұрын

Hi excellent teaching..it would be nice if could share sample of data that text file for our practice..

@zaydarendse2812 4 жыл бұрын

Thanks for the Vid Leila, always learn something new from you : ) …I’ve just got to figure out why Power Query sees numbers in my data upload as text…. Face palm moment for me…

@wayneedmondson1065 4 жыл бұрын

Saw your comment.. that has happened to me. Try first using TRIM on the column in question and then make sure to define data type as numeric (integer, whole number, currency, etc.). Sometimes during a transformation, numbers retain leading or trailing spaces which makes PQ then interpret the column as text. Hope it helps.. good luck!

@zaydarendse2812 4 жыл бұрын

@@wayneedmondson1065 Thanks Wayne :) your theory does indeed work!! here is a sample of what i need to split, (202006|APN|0605000111|205620 - Name Surname|VAT EXCL:APN SUBSCRIPTION=R8.76|DATA USAGE=15623.8600MB|DATA COST=R1093.67|INVNO=F314481619) from this i bet you can tell i'll first need to use text to columns, then trim, then upload into power query...its just too much PT... for now i'll just convert the column to Number after the query has uploaded into my file...however whenever i upload new data and the query updates.. i'll better remember to re-format my columns to number :(

@zaydarendse2812 3 жыл бұрын

solution to my issue was to Replace Value . (dot) with , (comma)....

@wayneedmondson1065 3 жыл бұрын

@@zaydarendse2812 That looks like a complicated transformation. Glad you solved it. Good luck!

@malithfernando7091 4 жыл бұрын

Patta

@lgvdbroek 4 жыл бұрын

Leila, as usual: S U P E R !!!! In the past, you could download your bank statements from your bank (the Netherlands) in an Excel-CSV file. That worked great. Now you will receive 12,341 PDF files. What madman came up with this .........😡

@alanmonaghan9194 4 жыл бұрын

Excel 365 insider now has a connector for PDF files. Data / Get Data / From File / From PDF

@888subash 4 жыл бұрын

Mam I like ur vedio,n I regularly watch, Mam I hv a question hopefully you will solve it. How can we lookup repeted name value in two different different table. Etc, ram repeted in 5 times n I want to lookup from different table which is also repeted there many times. Mam I use index n match formula n it's returne always firts value.

@anujjain5989 4 жыл бұрын

HI Leila, Thanks to share new logic, Currently i'm using X axis & Y axis rule in map, I'm facing big problem in same process, . I'm creating data in India Map, while using state & value in India Map , Value is not visible due to State size is very small , please confirm how stretch the size of Data label.

@franklucas9313 4 жыл бұрын

what about maps of smaller regions like forward sortation areas for a city. where would we find those?

@iduncanw 4 жыл бұрын

Clear and simple to follow, nice lesson. Do you know if this works as easy for other countries?

@LeilaGharani 4 жыл бұрын

I tried with some and it worked fine. But you should add the country as a separate column.

@iduncanw 4 жыл бұрын

@@LeilaGharani Thanks, that worked for Australia. Wonder if they will introduce support for suburbs/city's within a state.

@tuyoexcelypowerbi 4 жыл бұрын

Microsoft should include interactive map charts in its Excel Chart Library. I mean by interactive when you click in a state and asign a macro to it (specific state)

@mathijs9365 4 жыл бұрын

for reporting u should use powerbi

@jialu2669 Жыл бұрын

Hi Leila, thanks very much for sharing this great video. Anywhere we can download the workbook to follow this video please?

@LeilaGharani Жыл бұрын

Sure, just follow the link to the blog - link is in the description of the video.

@deepakmehra182 4 жыл бұрын

Awesome Technique !! Just one help - Is there anyway we can extract the distinct Maximum value (in Column B) of duplicate items (in column A) without using an array formula. My version - MAX(IF($B$2:$B$205=P2,$D$2:$D$205)). But this is an array function. Please help. Thanks in advance.

@honda7812 4 жыл бұрын

Can u assist with charts with dynamic references based on formulae?

@Ivi531 4 жыл бұрын

Great, thanks! in which cases your cvs file would be updated? can you link it to an api?

@LeilaGharani 4 жыл бұрын

Whenever you get new data in like a new month. Just know that Power Query does not update the source.

@Ivi531 4 жыл бұрын

Leila Gharani thank you! but I mean, that only happens when I manually add a line, correct? or does the csv update automatically from web data for example?

@patricksroyer8673 3 жыл бұрын

Hello. Can we create the local map (insert to Excel)??

@dannyleblanc336 3 жыл бұрын

can we create our own filled map?

@adriannacook313 4 жыл бұрын

Hi Leila, thanks for another wonderful video. Could you please let me know why when I clicked close and load to in PQ, I never had the options to choose load to PivotTable, Pivot table report or create connection only. I also didn’t have the option to close and load to exisiting worksheet. It always forced me to load to a new worksheet. All I had was load to a new worksheet as table. I’m using excel 2016 and office 365 is available. Thanks heaps.

@LeilaGharani 4 жыл бұрын

In Excel 2016 you only have limited options. If you try it in 365 you should see all these features.

@carlosnakid2551 4 жыл бұрын

Do you prefer PowerBI or Tableau?

@basudevagarwal39 4 жыл бұрын

Hi Leila.. is it possible to add 4 different charts in one sheet. Also I need to copy them to word for final presentation. But the format gets changed. Please help

@lompeluiten 4 жыл бұрын

I tried a map chart with the netherlands with pins for locations. I don't manage to get it right.

@nkowk 4 жыл бұрын

Please help. I didn't see Excel workbook file in the zip file, so I was unable to try it. Next question is the map file. Need to have map file with names to each state. How do I get this map file with each state as a picture object ? Thank you for your help.

@lollys2galaxy317 4 жыл бұрын

A little bug at the end , new mexico should be dark green 😁

@pinto1580 3 жыл бұрын

not really, dark green is for the forty thousands

@PolanskyMoreira 4 жыл бұрын

I would like to use a map chat with a especific map like farm region or some like that.

@OakleyTurvey 4 жыл бұрын

You can do this if you have point data and columns for Longitude and Latitude. You may have to use 3D Maps to get it to work as you require.

@dbc123cbd 4 жыл бұрын

@hariyantowidjaja6167 4 жыл бұрын

Can i plot cities of indonesian in this map?

@TheMozerfok 4 жыл бұрын

It would be nice if we knew what parameters can work for maps outside of the US. France for example is very inaccurate. I guess Europe as a whole as well

@emesefulop4989 4 жыл бұрын

Aham, Europe seems inaccurate( i tried with Hungary, some were ik, some not)

@mc4056 Жыл бұрын

The problem im having is how to capture individual addresses/zip codes within one state no video shows this! It wont map!

@ahan221 4 жыл бұрын

Hi how can I open a forgotten password file excel 2016

@shereensalah3199 3 жыл бұрын

How to solve the problem of not showing all the provinces of a country

@ricos1497 4 жыл бұрын

I'm guessing that this map chart doesn't work with the data model? I'm imagining a situation where you split your text file data into a city dimension table and a fact table with the data and then used the map as a slicer. Hopefully one day, Microsoft will make all charts into slicers in that way. Click on a data point in a bar chart and apply that as a filter elsewhere.

@brianxyz 4 жыл бұрын

I think they want you to use Power BI for stuff like that.

@nazmulhshohan5716 4 жыл бұрын

2nd

@hakanyetkin5424 3 жыл бұрын

Nice informations. If you want to create map chart online you can use paintmaps.com

@virginiareyes2723 4 жыл бұрын

@prasadkannan1112 4 жыл бұрын

Hai madam i have a doubt

@OlivierTravaglini 4 жыл бұрын

For interrested in Mapping Data try ArcGIS Maps for Office (free plug-in) much more option ! www.esri.com/en-us/arcgis/products/arcgis-maps-for-office/overview