Remove Random Null Values from Columns in Power Query

Подписаться 97 тыс.

Просмотров 45 тыс.

50% 1

Check out our newly launched M Language course ↗️ - goodly.co.in/l...
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/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co....
Corporate Training 👨‍🏫 - www.goodly.co....
Need my help on a Project 💻- www.goodly.co....
Download File ⬇️ - goodly.co.in/r...
===== 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!

Опубликовано:

28 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 139

@iSaveMe365 Год назад

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

@vwij1736 Год назад

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.

@FranzLorenzDoeringer Год назад

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

@pankajjoshi2952 9 месяцев назад

The comment I was looking for👍.... the corresponding data is disturbed in the final output

@williamarthur4801 8 месяцев назад

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.

@sushmitasingh3326 Год назад

Where was this video when I needed it the most 😢. Thank you so much

@GoodlyChandeep Год назад

Glad you finally found it Sushmita!

@RohitKumar-zm3nw 5 месяцев назад

@@GoodlyChandeep my question is if null is deleted will it effect the row data like will it mis match from pervious.

@scottcha03 Год назад

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

@Excel-power-users Год назад

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.

@nethikarnarendra4884 Год назад

Amazing solution. How do we tackle data that fall under different columns? Ex. Two date columns with random null values. Solution should be dynamic

@ShubhamSharma-ls6hj Год назад

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

@tariqhamadi4009 Год назад

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?

@jacobreddydevidi1305 8 месяцев назад

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

@kamil4177 Год назад

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.

@hichamhadj9640 11 месяцев назад

You're a life saver, thank you very much

@Ratnakumarwrites Год назад

You made it look like effortless... good presentation ... liked it...

@GoodlyChandeep Год назад

Thanks Ratna!

@ankitjainsss 8 месяцев назад

Right time seeing this video, thankyou

@Luciano_mp Год назад

Very good man! I liked. Thank you!

@GoodlyChandeep Год назад

Glad you liked it Luciano !

@cmlibin 3 месяца назад

how do I remove null values but retain the remaining values in same cells and not compress them

@robrayborn1349 Год назад

That's freak'n awesome! Thank you!

@GoodlyChandeep Год назад

Glad you like it Rob!

@chrism9037 Год назад

Awesome video Chandeep

@GoodlyChandeep Год назад

Glad you like this Chris!

@marvelstark3797 Год назад

can the transforming of column types into list can cater an infinite numbers of columns inside the table? thanks.

@eslamfahmy87 Год назад

that's a magic 🎩 excellent 👌

@geckostudiodotcom 4 месяца назад

that was awesome!! any other way to this if we don't want to use any M-Language?

@john-5288 3 месяца назад

After doing all this i still have null values in my data , why?

@alexhsieh8348 Год назад

Thanks!

@GoodlyChandeep Год назад

Thanks a lot Alex. Cheers

@santoshdevtale Год назад

Awesome Bro❤

@arbazahmad7177 Год назад

Great 👍

@z.719 Год назад

Awesome tricks❤

@GoodlyChandeep Год назад

Thanks!

@Rice0987 Год назад

Transpose - merge - transpose again.🤗

@Rice0987 Год назад

kidding of course :)

@mohammadalbizri2013 Год назад

Amazing

@GoodlyChandeep Год назад

Thanks Al Bizri

@kkingofkings Год назад

BC😀

@inkuban Год назад

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?

@ujjwalgupta7 Год назад

Still having null valus in all columns

@esthercraft 6 месяцев назад

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!!!!

@rodrig857 Год назад

This is exactly what I needed, now you are one of my PowerBI references, thanks!

@KuldeepSingh-nq1vi Год назад

Really Awesome Video , Chandeep You champ in Power Query.. No other one like you I have not seen so far.. ❤❤❤

@GoodlyChandeep Год назад

Thanks a ton Kuldeep!

@arpitsehgal5832 8 дней назад

hey man! Whoever you are! whatever name is yours! You're just wonderful excellant presenter actually and i can vouch for you to anybody for the best orator in any type of topic. Hats off! to you man. I have seen many videos but till now nobody just explained so simply and with easy steps like you did..🤗❤💯

@josh_excel Год назад

This is a good approach. I was thinking, of fill down or up, then remove duplicates, but this was good.

@Rice0987 Год назад

Chandeep uses the magic of Lists.🤗

@vivikanse6005 Месяц назад

what if i am combine data from multiple sheets and further i have to perform this operation how i can do that ?

@patrickhanrahan6559 3 месяца назад

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’….

@emmanuelndubuisi3315 2 месяца назад

Hi Thanks for this video, however, I tried doing this on my worksheet but i keep getting the error message that a cyclic reference was encountered during evaluation. Please can you help

@abulhossainripon4200 10 месяцев назад

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?

@maheshgupte1495 6 месяцев назад

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

@gayathrinagaraj3981 3 месяца назад

Hey! that a nice content.. have a question. what if only one of my column has random NULLs ? same code works there too?

@TheSystemsConsulting Год назад

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

@jeenajones9951 11 месяцев назад

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?

@nhatthanhma9466 Год назад

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)

@maheshgupte1495 6 месяцев назад

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

@MagedMaher Год назад

I want to remove null value of one column only in the table

@ramruttunaubeeluck9235 Год назад

Hi Do we need to have coding knowledge to work with power query M Language please?

@sampradhan5840 Год назад

Hello Sandeep, Data is mismatching from the original table

@holahandstrom Год назад

You continue to find 'common problems and deliver efficient solutions' - that's quality.

@victoriaagomuo3918 Месяц назад

Thank you so much. But its not as simple as you say it is😅

@norhayasaibi9563 Год назад

I wonder if it works for scane involves where the null is everywhere

@IvanCortinas_ES Год назад

The very best channel about Power Query. I don't know, but one of the best, without any doubt. Thank you Chandeep!!!

@GoodlyChandeep Год назад

Wow, thanks Iván!

@_badboikiller_ 9 месяцев назад

not working 3rd step explain ?????????

@gj4791 Год назад

Great!!!

@GoodlyChandeep Год назад

Thanks!

@SalmanKhan79 Год назад

why the face in thumbnail?

@NhiNgo-up8js Месяц назад

thanks you sm

@SandipanSarkar-c8v 6 месяцев назад

Finished watching

@OmisileKehindeOlugbenga Год назад

Awesome!

@abhijeetshetye3764 Год назад

Very useful video 👌🏻

@GoodlyChandeep Год назад

Glad it was useful Abhijeet!

@ashutoshanand250 Год назад

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.

@GoodlyChandeep Год назад

please see this link - goodly.co.in/products

@Rice0987 Год назад

Animation level is raised in several times! Respect!

@GoodlyChandeep Год назад

Thanks John!

@ghanshyamkundu1559 Год назад

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!

@HarshilPatel497 Год назад

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

@hayashaikh2267 Год назад

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.

@HEMENDRAPARIDA Год назад

Awesome

@roywilson9580 Год назад

Thanks for the video. I have getting data like that, especially from people who should know better :D

@roshanmonteiro3904 5 месяцев назад

Fill in up or down option would not solved this issue in the beginning?

@GoodlyChandeep 5 месяцев назад

Give it a try ;)

@jenzgames7076 7 месяцев назад

Thank you for this been looking for a week for this problem in my raw data

@mauv 6 месяцев назад

Only vid I've watched that solved the problem. thank you

@arunk.7080 Год назад

love ❤ all your videos. 👍👍👍👍👍✌️✌️✌️✌️

@GoodlyChandeep Год назад

Thanks Arun!

@kanthu1931 Год назад

There are more than 5 different date columns for a line item. How to calculate age from the latest date

@abdulrahmanbaamer8624 Год назад

Nice,, as usual. Is "Fill up" considered as a solution in this case? I think yes....

@vamshiabhilash Год назад

THIS never actually worked for me

@muhannadbasalem580 3 месяца назад

bro this is so clear - thanks a lot!!!!

@kaladin5802 7 месяцев назад

You just saved my day, thank you :)

@SamehRSameh Год назад

Marvelous ❤ we need more about M code for

@goodnewskasparyaodzramedo9097 Год назад

Good, exciting watching your videos

@zahir585 Год назад

Best of the best thank you my friend

@Rice0987 Год назад

Hey, i loved this one! THANKS!!! :)

@domarregala5932 Год назад

power query GOD!!!!

@GoodlyChandeep Год назад

Thanks Domar!

@alpeshpanchal7994 Год назад

Nice teaching method

@RonDavidowicz Год назад

Brilliant.

@GoodlyChandeep Год назад

Thanks Ron!

@zahoorsarbandi2982 Год назад

Superb and very much useful in daily office work.. Thank you very much for sharing knowledge.

@GoodlyChandeep Год назад

Pleasure Zahoor!

@raimundojs9547 Год назад

Yes, indeed, awesome! Thank you!

@user-bk8db7ud6k Год назад

Wizard.

@GoodlyChandeep Год назад

Thanks!

@henrypraise4873 Год назад

Really Awesome

@mirrrvelll5164 Год назад

Pff man, this was a great trick!!! I think I am gonna implement this into my reports =)))

@GoodlyChandeep Год назад

Awesome Mirrr!

@strangersingh6770 Год назад

Nice

@renewahner8086 Год назад

Thank you!

@gospelmoto2833 Год назад

awesome! badly needed this. BIG Thanks!

@GoodlyChandeep Год назад

Glad it helped Gospel!

@pierresonkeng6827 Год назад

Very interesting approach. Thanks

@GoodlyChandeep Год назад

Glad you liked it Pierre !

@izzat_kiswani Год назад

👍👍👍👌

@johnwabaki7125 Год назад

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.

@GoodlyChandeep Год назад

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

@johnwabaki7125 Год назад

@@GoodlyChandeep Thanks let me do it

@johnwabaki7125 Год назад

@@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