Remove Random Null Values from Columns in Power Query
Вставка
- Опубліковано 29 бер 2023
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
This video will help us learn to deal with random Null Values across the columns!
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/remove-random-nu...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - Наука та технологія
Wish I would have known about this sooner. Would have saved me a bunch of steps on all my other workbooks! Thank you for sharing
You continue to find 'common problems and deliver efficient solutions' - that's quality.
This is exactly what I needed, now you are one of my PowerBI references, thanks!
i saw this same power query problem but the way u do yours is easy, bcuz u make it a step by step approach slowing explaining each step. thanks man
This is MOOOOOREEEE Than Awesome... i was working an entire day + burning midnight oil trying to solve this.. and in a 6mins video, you solve it!!!!
Yes, indeed, awesome! Thank you!
You're a life saver, thank you very much
bro this is so clear - thanks a lot!!!!
Only vid I've watched that solved the problem. thank you
Thank you for this been looking for a week for this problem in my raw data
Hey, i loved this one! THANKS!!! :)
Good, exciting watching your videos
Thank you!
The very best channel about Power Query. I don't know, but one of the best, without any doubt. Thank you Chandeep!!!
Wow, thanks Iván!
Best of the best thank you my friend
Right time seeing this video, thankyou
Thank you so much .
If we have some data nill and repeat the headers row because the data get from more sheets with different formats
Is that way will be solve the case?
awesome! badly needed this. BIG Thanks!
Glad it helped Gospel!
Nice teaching method
You just saved my day, thank you :)
Very good man! I liked. Thank you!
Glad you liked it Luciano !
Awesome!
that's a magic 🎩 excellent 👌
Really Awesome
Marvelous ❤
we need more about M code for
Superb and very much useful in daily office work.. Thank you very much for sharing knowledge.
Pleasure Zahoor!
This is a good approach. I was thinking, of fill down or up, then remove duplicates, but this was good.
Chandeep uses the magic of Lists.🤗
Awesome Bro❤
Thanks for the video. I have getting data like that, especially from people who should know better :D
Animation level is raised in several times! Respect!
Thanks John!
Hey! that a nice content.. have a question. what if only one of my column has random NULLs ? same code works there too?
Hey i am coming across a same situation where out of 4 columns in a hierarchy of a table lets say estate is having block, block is having core and core is having flats. it's doesnot necessary that the lower level will always have the upper level data, it can be null. so in matrix when i am showing grouping the items are coming null and inside it the values are there. so i need to skip those. basically if an estate is not having block and core but having flat it should directly show the flat not in 4th level. if it is having block and flat, it should show estate block and flat. any idea how to achieve this, will really appreciate it. Thanks!
Hello Chandeep, Thankyou for sharing the precious knowledge you have gained over the years that too free of cost. Recently I was looking at my Mutual fund Consolidated Statement which is only available in PDF and I am not able to clean it data in the statement. Can you help us with it.
That's freak'n awesome! Thank you!
Glad you like it Rob!
Great 👍
Really Awesome Video , Chandeep You champ in Power Query.. No other one like you I have not seen so far.. ❤❤❤
Thanks a ton Kuldeep!
Thank you for sharing this tutorial, I have data with Mutiple columns. I want to get count blank cell in each rows in a separate column. Please help
Thanks for the video. One observation though. In the original table, Record with Tran ID101J11 had null for the Region, but in the processed table the same record had Mumbai under the Region field.
Very interesting approach. Thanks
Glad you liked it Pierre !
here you remove the nulls, if i want to replace the null with its immediate top header is it possible in power query? please make a video on it.
explaining the issue: in excel we can do it by selecting the blanks then "=", then "top header", then Ctrl+enter. is it possible in powerquery?
Awesome
love ❤ all your videos. 👍👍👍👍👍✌️✌️✌️✌️
Thanks Arun!
This is awesome, I really love this
I'm glad you like it Lucena!
Where was this video when I needed it the most 😢.
Thank you so much
Glad you finally found it Sushmita!
@@GoodlyChandeep my question is if null is deleted will it effect the row data like will it mis match from pervious.
Thank you, this is such an annoying problem, sometime you can get away with unpivot / re pivot, but often this does not work due to aggregation.
Amazing solution. How do we tackle data that fall under different columns? Ex. Two date columns with random null values. Solution should be dynamic
Thanks!
Thanks a lot Alex. Cheers
You made it look like effortless... good presentation ... liked it...
Thanks Ratna!
What if in the list view of all columns as list, some of the sublists are empty in whole? When I transpose to table view I get again those null columns.
Very useful video 👌🏻
Glad it was useful Abhijeet!
I wonder if it works for scane involves where the null is everywhere
Finished watching
Great!!!
Thanks!
Hi
Do we need to have coding knowledge to work with power query M Language please?
Incredible!
Thanks Ivan!
can the transforming of column types into list can cater an infinite numbers of columns inside the table? thanks.
I was trying to do it using calculated column using countblank function but it is count of all blank in column instead of row wise blank
Hi Chandeep, if i have 1 column have no null ( that key column got duplicate because of null like your example ) and how could i do your way and keep the that column ( because when i do your way , it doesnt match with the column have no null)
Nice
Nice,, as usual.
Is "Fill up" considered as a solution in this case? I think yes....
Awesome video Chandeep
Glad you like this Chris!
Brilliant.
Thanks Ron!
Awesome example but it look like simple. Can you make one more vedio on this type when null is not constant but at random places
Great.....
Thanks Bulbul!
Thanks for this. Awesome. However, would this method solve something like the below? As it's going to merge different firstnames to different surnames? If for any reason the null names could not be found then is it best to delete the entire row? Many thanks.
Firstname Lastname
1 John
2 Baba
3 Ahmed
4 Falil Johnson
5 Mercy
6 Curly
7 Ben Tinytoe
Awesome tricks❤
Thanks!
You would be shifting cell values up an down and that would break the key assignment that the value should be linked to. Or am I getting this wrong?
Hi i have a folder which contain large csv files each approx 25 mb size... I am getting the data from folder then i am combining all files in one query, then i am doing some stuff and remove duplicates and some grouping task... the issue is my query take long time to refresh some time i kept on and till morning this does not solve the issue, can you suggest here
superb!!
Thanks Jaipal!
How accurate is this? What if the data in each row represents each country? This is similar to Fill Up, and would create inaccuracy. In this case, what is my better option, to represent them in power BI?
Hello Chandeep - you do not loose an entry, but the problem is that the rows do not match (compare start table, with the result). Unfortunatly I can not attach the picture where I did the comparison. But it is not a solution, when the relation (rows) to not match.
Thx for clearing
franz
The comment I was looking for👍.... the corresponding data is disturbed in the final output
I want to remove null value of one column only in the table
Pff man, this was a great trick!!! I think I am gonna implement this into my reports =)))
Awesome Mirrr!
Hello Sandeep,
Data is mismatching from the original table
Amazing
Thanks Al Bizri
Yes this is "awesome"
Glad you like it Sandeep!
Can you make solution video for
Null value calculation for all the column in a table dynamically.
Suppose you have 500 columns and not sure which column has null values or not so calculate% of null values for each and every columns in a table.
There are more than 5 different date columns for a line item. How to calculate age from the latest date
how do I remove null values but retain the remaining values in same cells and not compress them
In actual use case
when data comes from piple line
ACID properties are maintained
if not DataWarehouse
ODS layer is obsolute
this method can be a sample use case but not real time data that is used at ORG level
@goodly
Hello Chandeep!
Ignore missing column while summing column.
My source data consists of following 3 columns C1, C2 and C3. I add these 3 columns to get an extra column called Sum.
The problem is the source data may or may not contain all 3 columns all the time. There are instances when C1 and C2 is present but not C3; C2 and C3 is present but not C1; none of C1, C2 and C3 are present.
I am typing this on my phone and might make some errors but this is how you should approach it.
1 convert the record to a list
2 remove nulls from the list
3 sum the list
M code would look like this.
In a new column
List.Sum(List.RemoveNulls(Record.ToList (_)))
I am assuming there are only numeric columns in your data.
Hope this helps
@@GoodlyChandeep
Thanks let me do it
@@GoodlyChandeep Doesn't work Chandeep, i try a try ... otherwise null i get nulls instead of summing the present columns values, also i tried MissingField.Ignore still i get an error.
Note:
"I have a lot of columns more than 60 so may increase or decrease depend on the date "
drive.google.com/file/d/1-ZXUQndQapqhk0O6_lteaFG0Y4dETWUw/view?usp=sharing
I wonder if fill up on one column and then filter null on another would work? No M required…but your way is obviously better more dynamic And you get to use M….. wish I had that skill in my ‘bag’….
Fill in up or down option would not solved this issue in the beginning?
Give it a try ;)
That is *exactly* how I would have done it.
Good stuff! :-)
Thanks Geert!
After doing all this i still have null values in my data , why?
👍👍👍👌
Still having null valus in all columns
not working 3rd step explain ?????????
power query GOD!!!!
Thanks Domar!
Transpose - merge - transpose again.🤗
kidding of course :)
Hi I want to take full power bi course from you how can i enroll can you please give me the details or any contact number.
please see this link - goodly.co.in/products
THIS never actually worked for me
Wizard.
Thanks!
why the face in thumbnail?
BC😀
that was awesome!! any other way to this if we don't want to use any M-Language?