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/
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?
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.
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.
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 🤓
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!!
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
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.
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.
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 .........😡
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!
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.
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)
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…
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!
@@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 :(
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.
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.
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
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!
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.
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.
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?
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
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.
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
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.
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
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/
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 🦸♂️
Bought the course and highly recommend. Helped persuade my company to move from Office 2013 to finally move to 365. Thanks!!
Signed up yesterday on Udemy. Please make a similarly comprehensive, Beg. to Adv. course for Power BI as well.
How to import live data excel to spreadsheet? Please help me. My e-mail adress is melikov_xalid@yahoo.com
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?
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.
Well well well, you're the best excel tutor out there.
a reliable and knowledgeable tutor. I always follow her posts and save them for later reference.Thank you daughter.
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.
Wow, thanks! I'm glad you like the course!
Leila Gharani how can I contact you ? There’s something I would like to ask you about Excel
TRUE 💯
It's Thursday, it's time for another great video from Leila! This Power Query tool will change my life...
Great to hear! 😘
I just found you on RUclips and the quality of your RUclips videos are stunning! Keep it up!
Thank you! Will do!
Thank you so much Ma'am,🙂 you've made my life easier by understanding how to work smart on MS Excel👍🏼
Most welcome 😊
the best excel tutor love all your vidoes
Thank you leila
Well Done
Me too
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 🤓
🤗 Hope you'll enjoy the rest of the course!
Thank you for sharing with iNet community.👍
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!!
Thanks for sharing these tips, Wayne!
i love what u did!!
If it is Leila teaching you, it has to be the best! So here she comes again with another great video! Keep them coming... :-)
More to come :)
*Great Lessons on every Great Thursday!*
😘
The Queen of Excel is back looking amazing as ever and with info at her fingertips. Brilliant example very well explained. Thank you.
😘
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
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.
Glad you like it. Definitely was a nice evening 😊
Thank you Leila, PQ to the rescue again!
your videos are fantastic. His teaching didactics are simple and objective. congratulations
OK 2:50 is mind-blowing. Group by in Power Query. "Like a pivot table in Power Query."
Fan of LG with PQ Always
Explaining PQ in Supey easy way
Thnx
So nice of you
Easy as 1 2 3 .....thanks again. very much appreciated. Excel is such a powerful program when applied for the Applications.
You're very welcome, Peter!
Thanks, awesome tutorial as always, I have enrolled into the course!
Cool, thanks! Hope you'll enjoy it!
Your boss needs it right now - What are you going to do?
(Hire Leila)
:D
😁
PQ makes it so easy.. Thanks Leila
My pleasure, Matt!
The Queen strikes back with a map chart and power query!
😘
Leila Gharani Hi Leila, hope you are well and safe.
Will you provide the course for power BI?
Last question Are you kurdish?
Thanks
Excellent explanation Leila. Thank you very much for sharing your knowledge.
My pleasure, Iván. Hope it will come in handy for you.
As usual... Best in Sharing Knowledge....... Thank you Leila!!
My pleasure!
Wonderful explanation 👏
Thank you 🙂
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.
Great job!
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 .........😡
Excel 365 insider now has a connector for PDF files. Data / Get Data / From File / From PDF
PQ Map-erific, Teammate : )
I learned so much from YOU too 🕴
YOU MVPs should pool even more ➡ the data processing future is coming VERY SOON 🔜 🍀
Thank you teammate Mike :)
I learned so much from YOU Mr. Mike ;)
You are the best
😘
Hi Leila!Great Tutorial,Power Query Makes Even The Most Disorganised Data Really Easy To Work With..Thank You :)
It sure does Darryl :)
Another win, thank you ma'am
Simple and great Leila ♥
Thank you. It really works🤩
I LOVE U so much , Keep Rocking 👌👍👍👍👍
Thank you so much 😀
Impressed and inspired
Glad to hear that :)
That was REALLY good 👌
Thank you, great tutorial
Nice tutorial 👌
Very useful information
Very helpful... thanks!
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!
Thank you so much for this informations. 😁😁
Good Tool
Many thanks!!!
Wow i loved it!!!!
thanks
Amazing Leila. Thanks. : ) : )
Perfect
Very informative mam :)
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.
Thanks wonder woman
Hi excellent teaching..it would be nice if could share sample of data that text file for our practice..
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)
for reporting u should use powerbi
Amazing Excel, you are so pretty. Regards from Cali-Colombia.
😘
Very nice. Can this be done for every country individually?
And can i do other areas such as states, regions, suburbs?
Thanks. Much appreciated
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…
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!
@@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 :(
solution to my issue was to Replace Value . (dot) with , (comma)....
@@zaydarendse2812 That looks like a complicated transformation. Glad you solved it. Good luck!
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.
You are amazing wow.
Hi Leila, thanks very much for sharing this great video. Anywhere we can download the workbook to follow this video please?
Sure, just follow the link to the blog - link is in the description of the video.
Clear and simple to follow, nice lesson. Do you know if this works as easy for other countries?
I tried with some and it worked fine. But you should add the country as a separate column.
@@LeilaGharani Thanks, that worked for Australia. Wonder if they will introduce support for suburbs/city's within a state.
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.
Nice !
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.
A little bug at the end , new mexico should be dark green 😁
not really, dark green is for the forty thousands
Patta
Nice video i learn new things i want ask i can use the same idea on custom map i created
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
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!
Hello. Can we create the local map (insert to Excel)??
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.
Can u assist with charts with dynamic references based on formulae?
Do you prefer PowerBI or Tableau?
what about maps of smaller regions like forward sortation areas for a city. where would we find those?
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.
In Excel 2016 you only have limited options. If you try it in 365 you should see all these features.
Great, thanks! in which cases your cvs file would be updated? can you link it to an api?
Whenever you get new data in like a new month. Just know that Power Query does not update the source.
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?
can we create our own filled map?
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
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.
I tried a map chart with the netherlands with pins for locations. I don't manage to get it right.
How to solve the problem of not showing all the provinces of a country
The problem im having is how to capture individual addresses/zip codes within one state no video shows this! It wont map!
A
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
Aham, Europe seems inaccurate( i tried with Hungary, some were ik, some not)
Can i plot cities of indonesian in this map?
I would like to use a map chat with a especific map like farm region or some like that.
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.
Nice informations. If you want to create map chart online you can use paintmaps.com
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.
I think they want you to use Power BI for stuff like that.
Hi how can I open a forgotten password file excel 2016
2nd
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
First
This is new to me . As I'm not that good in maps 😂😂
Welcome your highness
Welcome back! I missed your comments :)
@@LeilaGharani so busy for a while
Looking forward to join your power query/dax course . Thanks for having you in that article
🤩🤩🤩🤩
Hai madam i have a doubt
Right now is not 8 minutes later.