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.
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 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!!
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.
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 🤓
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 .........😡
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)
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!
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 :(
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.
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.
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.
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!
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.
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.
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.. 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
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?
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.
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.
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
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.
a reliable and knowledgeable tutor. I always follow her posts and save them for later reference.Thank you daughter.
Well well well, you're the best excel tutor out there.
I just found you on RUclips and the quality of your RUclips videos are stunning! Keep it up!
Thank you! Will do!
It's Thursday, it's time for another great video from Leila! This Power Query tool will change my life...
Great to hear! 😘
the best excel tutor love all your vidoes
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 💯
*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
your videos are fantastic. His teaching didactics are simple and objective. congratulations
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!
Thank you so much Ma'am,🙂 you've made my life easier by understanding how to work smart on MS Excel👍🏼
Most welcome 😊
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 :)
Thank you for sharing with iNet community.👍
As usual... Best in Sharing Knowledge....... Thank you Leila!!
My pleasure!
Excellent explanation Leila. Thank you very much for sharing your knowledge.
My pleasure, Iván. Hope it will come in handy for you.
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
Fan of LG with PQ Always
Explaining PQ in Supey easy way
Thnx
So nice of you
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 😊
PQ makes it so easy.. Thanks Leila
My pleasure, Matt!
Thank you leila
Well Done
Me too
i love what u did!!
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!
Wonderful explanation 👏
Thank you 🙂
Thank you Leila, PQ to the rescue again!
Thanks, awesome tutorial as always, I have enrolled into the course!
Cool, thanks! Hope you'll enjoy it!
OK 2:50 is mind-blowing. Group by in Power Query. "Like a pivot table in Power Query."
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
Thank you. It really works🤩
Simple and great Leila ♥
Great job!
Impressed and inspired
Glad to hear that :)
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 ;)
That was REALLY good 👌
I LOVE U so much , Keep Rocking 👌👍👍👍👍
Thank you so much 😀
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.
You are the best
😘
Your boss needs it right now - What are you going to do?
(Hire Leila)
:D
😁
Nice tutorial 👌
Thank you so much for this informations. 😁😁
Very helpful... thanks!
Very useful information
Amazing Excel, you are so pretty. Regards from Cali-Colombia.
😘
Amazing Leila. Thanks. : ) : )
Wow i loved it!!!!
Thank you, great tutorial
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
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
Many thanks!!!
Very informative mam :)
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.
Perfect
Good Tool
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.
You are amazing wow.
Thanks wonder woman
Hi excellent teaching..it would be nice if could share sample of data that text file for our practice..
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.
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!
Patta
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!
Can u assist with charts with dynamic references based on formulae?
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.
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.
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.
Hello. Can we create the local map (insert to Excel)??
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!
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.
Very nice. Can this be done for every country individually?
And can i do other areas such as states, regions, suburbs?
Thanks. Much appreciated
what about maps of smaller regions like forward sortation areas for a city. where would we find those?
Nice !
Nice video i learn new things i want ask i can use the same idea on custom map i created
can we create our own filled map?
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.
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.. 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
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?
I tried a map chart with the netherlands with pins for locations. I don't manage to get it right.
Do you prefer PowerBI or Tableau?
The problem im having is how to capture individual addresses/zip codes within one state no video shows this! It wont map!
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.
A little bug at the end , new mexico should be dark green 😁
not really, dark green is for the forty thousands
How to solve the problem of not showing all the provinces of a country
Hi how can I open a forgotten password file excel 2016
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.
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.
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)
Hai madam i have a doubt
Nice informations. If you want to create map chart online you can use paintmaps.com
2nd
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
🤩🤩🤩🤩
A
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
Right now is not 8 minutes later.