Video není dostupné.
Omlouváme se.
Data Cleaning PRO Tip: Dealing with text & numbers in same column
Vložit
- čas přidán 18. 08. 2024
- Recently I had to deal with a weird data clean up scenario. My client (a large hotel chain) was getting room booking data with mixed format. The booking date column occasionally has a text value which needs to extracted and used for proper analysis. In this short tip video, let me show you how to deal with such problems using Excel.
⏱Video topics 👉
===============
0:00 - The text & numbers problem
0:39 - Avoiding the problem by removing text data
2:32 - How to separate and properly arrange data (with Power Query)
📗Sample file
============
If you want to practice this concept, here is a sample workbook.
chandoo.org/wp...
👩💻👨🎓 LEARN MORE about Data Cleaning
=================================
📺 CZcams VIDEOS:
on Power Query (complete tutorial, 80 mins, 260k views) - • Power Query Tutorial -...
on Data cleaning (top 10 tips, 15 mins, 300k views) - • Data cleaning in Excel...
More data cleaning (10 more tips, 15 mins, 50k views) - • Data Cleaning in Excel...
👩💻 COURSES:
💥Power Query mini-course (2 hours, full PQ with many practical examples) - chandoo.org/wp...
📚FULL Excel (24+ hours, 100s of examples) - chandoo.org/wp...
~
A data analyst walks in to the bar. A pretty lady smiles at him and walks over. She says, "You look cool. Here is my number. Text me your number."
The data analyst replies, "Sorry, but I don't TEXT() numbers" 🤦♂️😂
#datacleaning #excel
![19 Excel Shortcuts you NEED to use more [with FREE PDF 📃]](http://i.ytimg.com/vi/Q8fghk3DQxY/mqdefault.jpg)
Just checking... Do you want to see a Pandas tutorial on how to clean this data? Like the comment if you want to.
That's awesome
Looking forward 👌🏻
YES, definitely
yes, this is some next level for data analytics videos well done
Yes please
Small token of appreciation. Several views lately and almost always save to my excel playlist. And you’re so entertaining as well. Even a rugby reference in a video!
Hey @Hopspowered... thanks for that lovely gesture and appreciation of my work. 😍
I never comment on any YT video, but your videos are really interesting and informative. Kudos to such content and Thanks for this
I appreciate that!
I am lucky to have a teacher like you. keep up the good work.
Hi Chandoo,I love 2nd technique .This will my life easy than before.Thank you very much.
2nd solution is awesome, I was thinking how data can be retained while it is being removed 😀.
I am glad you liked to Nirmal :)
easy but yet very valuable. thannk you
You are welcome Khalid.
Learned power query from you and will keep learning from you. Boom
More power to you Arun ⚡
THANK YOU!! So many accounting softwares extract ledgers like this (with account heads between dates) and it has been so frustrating until now. This is REALLY helpful!
I was familiar with the first technique but today I learned the second technique. I'm becoming awesome. Thanks Sir
Excellent
Great!!! Thank you Chandoo!
I might have not done this using power query [might have used fiters and delete]......a really nice application. 👍
Hi Chandoo, Thanks a lot. i was looking for this solution and found this video by God's grace. Excellent, Appreciate it Bro.
Just started my journey into data analytics, thanks for these really interesting/intuitive videos!!
Great video as always! Thanks Chandoo!
Glad you enjoyed it Chris.
good tips on data cleaning, Can't wait to apply this knowledge practically.. 👌🏻
You are welcome :)
Amazing solution!! How to text.combine for only single column e.g. QnA in multiple rows separated by ? Delimiter (Q ends with?)
Thank you Chandoo 🙏
You are welcome Saviss.
As always awesome!!
Nice to learn 🙏
Great approach for people trying to transition to power query and later on dax. I loved how you gave the1st option for excel beginners as well as 2nd one for intermediate learners. Cant wait to see how will you transition to simple dax!!!!!
You are welcome Tina. The rule when it comes to DAX is simple. Try to solve data problems first with SQL / Power Query or other source systems. If you try to develop DAX solution for this problem, you will do a lot of unnecessary stuff.
@@chandoo_ thank you Chandoo!!! I agree regarding power query - dont know how to start w swl really. For dax - maybe you could create sime simple intro to creating formula and writing code here - if you see the need. Not necessarily for this case but maybe other use cases.
Amazing stuff 🎉!
Great stuff, Chandoo!
You are welcome Jeremy :)
Ty chandoo, love u
😍😍😍
Nice one. Thanks!!
Great video!!✌
To solve with a formula:
=LET(ar,A2:D196,h,A1:D1,d,A2:A196,f,D2:D196,
a,IF(ar="","",ar),s,SEQUENCE(ROWS(a)),
x,f="",
k,XLOOKUP(s,FILTER(s,x),FILTER(d,x),,1),
VSTACK(h,FILTER(HSTACK(a,k),NOT(x))))
And dates are dates (numeric), and room numbers , numbers, so we can do other filtering etc
Forgot to name the last column:
=LET(ar,A2:D196,h,A1:D1,d,A2:A196,f,D2:D196,t,"Text Value",
a,IF(ar="","",ar),s,SEQUENCE(ROWS(a)),
x,f="",
k,XLOOKUP(s,FILTER(s,x),FILTER(d,x),,1),
IFNA(VSTACK(h,FILTER(HSTACK(a,k),NOT(x))),t))
Good one... 😎
Love this!!
😎😎😎
एक्सीलेंट tricks 👍👍
Omg thank u! This is so useful!
Glad it was helpful!
@@chandoo_ - I use various computing codes prepare reports but nothing can compete with excel when it comes to user friendly funtions! Excel team has done a great work in last 2-3 years!
YES!!!
Sir u don't need go to special. Just simply apply filter and choose blanks and press ctrl+ - boom.
😍 Love it.
@@chandoo_ is this sarcastic reply 😃😃
Wow, Your video help me a lot :)
Thanks Elfrid.
This is great!!
I'm also looking for a formula for the following:
This is my issue: I need to keep my employees at 40 hours a week. So if on Thursday I do the time cards and let's say Monday thru Wednesday they are at 30 hours already. Assuming that on Thursday they will do 8 hours and they will only have 2 hours left for Friday; at what time on Friday they will go home when they get to 40 hours?
So the formula that I need is:
Accumulated hours ➕️ today's 8 hours; hours left for Friday equals at what time he will go home on friday..
Help!
You are welcome CDWU.
You can use a formula like this to see how many hours they will have for Friday.
=MIN(8, MAX(32-hourssofar, 0))
You can then add that to starting time to see what time the employee can leave on Friday.
your content is good always.thanks
Glad you think so!
Genuis! 👏
Hi. Chando. Let's do your 2nd technique with an another simply solution.
Firstly please add a column at the beginning of the page. you will scroll your columns. i mean your A column will be your B column. i don't know but CTRL+R didn't work while our single values in column A. i can reference cells better with this :)
1) filter blank cells from C,D or E columns.
2) choose your values from B column, and choose cells from F column in the same range.
3) Then press CTRL + R.
4)Then remove the filter. and refilter your values instead of blank cells from column C, D or E.
5) write "=F3", in cell F2 and scroll it down.
And ta taaa :)
Good one. 😎
Superb. Learned something new!
Awesome 👌
🔥🔥🔥
Crazy tip
IT IS!!!
Thank you 🙏
😍
Hey Chandoo,
Thank you for the great work you do and for your videos that are beyond helpful. I have a problem that maybe you can solve. If I wanted to sort quarterly dates in the format of 1Q22 (quarter, quarter letter indicator, year) from earliest to latest, how would you go about it?
You are welcome Miguel. If you just have text values of 1Q22, 2Q22, 1Q21, 3Q20, etc. in a column, you can use this formula (Excel 365).
=SORTBY(data, RIGHT(data,2)+0, LEFT(data,1)+0)
But a better option would be to extract the Q & Year and then use Sort options and set up two level sorting if it is a one off thing.
It is awesome.
Great!
Danke!
😍😍😍
Thanks, Chandoo! Question: HowWouldYouAddSpacesAtCapitalLetters?
In Power Query, you can split text when the case changes. You can then "merge" these columns with space as delimiter.
A formula, that works for any array/range:
=REDUCE(range,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,TRIM(SUBSTITUTE(v,x," "&x))))
Good one. Donut for you 🍩
One more I come up with...
=TRIM(REDUCE("", MID(B2,SEQUENCE(LEN(B2)),1),LAMBDA(a,v,a&IF(EXACT(v,UPPER(v))," "&v,v))))
@@chandoo_ Super cool!! But what is "range" ??
If in B2 we set the "sentence" we want to "split" then range should be an empty string "" like in:
=TRIM(REDUCE("", MID(B2,SEQUENCE(LEN(B2)),1),LAMBDA(a,v,a&IF(EXACT(v,UPPER(v))," "&v,v))))
This works fine for a single cell.
My "range" can be column/row vectors or 2D arrays 😉
Your idea with EXACT is to be used if we have unusual capital letters like Ñ or others that are not found in an usual alfabet. So, embracing your idea with EXACT, a bullet proof formula that will work with any chars and also handling 2D arrays can be:
=MAP(range,LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m))," "&m,m))))))
For a single cell, everything that is after LAMBDA can work as a formula, no REDUCE needed
=LET(x,B2,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m))," "&m,m))))
So , team work won. Half of donut to you. ✌😉
My bad. Should have been REDUCE("" instead of REDUCE(range...
and yeah, I never eat half a donut. 😂
I am French teacher , how I use French mix up with Ms Excel to get freelancing job
will you please give a suggestion for me
What if there are dates (text datatype), instead of some words in the date column .. and there might not be any blank cells..
How to solve this .. ????
Is it must to use SQL for this or can we do it by using EXCEL OR POWER BI
Very valuable content on your you tube.
I have an data of 1000 rows and 500 column and want to modify the data of random 200 rows ..i am filtering 200 rows using normal filter manually...is there any alternative to select 200 rows quickly ?
Awesome
Chandoo, in your conditional column, what does it mean when you select Output of Date? I am confused by this since date isn't the actual output in the conditional column. I hope you see this and respond...many thanks!
Thanks for your question. The "Date" is just a placeholder for us to know which rows have dates and which ones don't.
How fill up works. Will it reads the unique value as null and fills up. Can it be blank instead of null and will it works still.
I haven't tried it with blanks. But as far as I know, I think it just reads nulls and replaces them with the value from below. Kind of like bfill and ffill in Python Pandas.
Hi Chandoo,
I want to Your help, in my current role, I am facing lack of store produce issue, please tell me, where will I learn more Advanced Store produce ? Swift response
Not sure what you mean by "store produce". If you meant stored procedures, I am not the person to help. I don't use them.
Super
Hi Chandoo !!
I need help in creating a macro which can combine data from different tabs in one. However the header keep changing for files.
Sometime it will have header in 2 rows sometime in 3.
I want to write a macro which will check how many rows are header and from next row it will copy the data and paste in the main sheet with same header format.
Please help.
Hey Chandoo,
Hope you're doing well...
I have around 1K of Google Maps hyperlinks in my excel file from which I must extract the latitude and longitude of each. Considering that there are thousands, it can be quite challenging to do it manually...
In any future video or in the comments, if you would be so kind as to share any formulas or tricks, that would be great.
Thanks...
You can use the TEXTSPLIT() function (new in Excel 365) for these kinds of tasks or Power Query. I suggest learning a bit more about PQ using this video - czcams.com/video/PiFAa_jjaEI/video.html
Hello Chandoo. I follow your YT videos these are very informative. I have a unique problem in power bi. In which I want to calculate date from a date column by user input parameter. But that column doesn't get updated when I change user input parameter. Can you tell me how I can resolve this.
Hi Omkar.. the column calculations don't update in runtime. They are calculated and set in model at the time of creation.
Hi Chandoo , seen your so many videos and it's very inspiring and informative for new commers .... It will be helpful if you could help me out with one problem or any experts viewers most welcome. I'm facing the weird issue , a client sent me file which has a its own theme but when I try to open same in my pc themes is getting changed to default automatically.
Any suggestions how to stop default theme and keep the theme to client one🙇🙏
You are welcome Amit. I have never noticed this kind of issue. I suggest posting about it on a forum like the official Microsoft help site or stackoverflow.
@@chandoo_ Thank you very much for reply ❤️😊
But company row are still get some blank value
That is expected as not all hotel bookings will be for companies.
@@chandoo_ can you offer any assignments because my passion to become a data analyst and now I am work on it but I don't no how to implement all that I have learned so please help me to give any assignments like that chandoo bro
Can you help me with cleaning data. Where can I ping you???
I am not available for any 1:1 help Reza. Please post your questions in a forum.
Hello chandooo
Hello Art
🤯
You are awesome in excel , but your courses are too costly :D :D
Thanks SR. That is ok. My courses are not for everyone. I believe knowledge should be free. But my bank says free videos are not an acceptable form of payment for mortgage. So I charge for some of my content.
@@chandoo_ yes you should charge for your expertise
I can’t believe he used personally identifiable data here. These are real people with real hotel stay dates. This has to be a violation of privacy or law. You should take this video (which is an excellent video btw) down and change the names in the data and reupload a new video
He is a professional, definitely he used fake data. There are a lot of random list generators engines online for names and almost any type of data. Hint: When names and surnames of same person look like they are from distinct cultures roots it is obvious that are random generated names.
Thanks for your concern. This data is randomly made up. Why would you think these are real people.
Chandoo please don't ruin your videos with memes 😥
Hmm.. what is so ruinous about a joke or two? This is how most of my videos (and even articles) have been.