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..🤗❤💯
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.
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?
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.
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!
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?
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)
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
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.
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
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 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
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
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?
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
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.
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
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’….
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!!!!
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
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
You continue to find 'common problems and deliver efficient solutions' - that's quality.
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!
This is exactly what I needed, now you are one of my PowerBI references, thanks!
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.🤗
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..🤗❤💯
Animation level is raised in several times! Respect!
Thanks John!
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.
Thank you for this been looking for a week for this problem in my raw data
Only vid I've watched that solved the problem. thank you
Superb and very much useful in daily office work.. Thank you very much for sharing knowledge.
Pleasure Zahoor!
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.
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.
You made it look like effortless... good presentation ... liked it...
Thanks Ratna!
Really Awesome Video , Chandeep You champ in Power Query.. No other one like you I have not seen so far.. ❤❤❤
Thanks a ton Kuldeep!
You're a life saver, thank you very much
Amazing solution. How do we tackle data that fall under different columns? Ex. Two date columns with random null values. Solution should be dynamic
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?
Hey! that a nice content.. have a question. what if only one of my column has random NULLs ? same code works there too?
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.
bro this is so clear - thanks a lot!!!!
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
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
Marvelous ❤
we need more about M code for
awesome! badly needed this. BIG Thanks!
Glad it helped Gospel!
Great Video ! thank you very much, you earned a new sub
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!
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?
Good, exciting watching your videos
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?
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)
Yes, indeed, awesome! Thank you!
Hey, i loved this one! THANKS!!! :)
Thanks for the video. I have getting data like that, especially from people who should know better :D
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
love ❤ all your videos. 👍👍👍👍👍✌️✌️✌️✌️
Thanks Arun!
Very good man! I liked. Thank you!
Glad you liked it Luciano !
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.
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
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
Very useful video 👌🏻
Glad it was useful Abhijeet!
Best of the best thank you my friend
Right time seeing this video, thankyou
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 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
Still having null valus in all columns
what if i am combine data from multiple sheets and further i have to perform this operation how i can do that ?
You just saved my day, thank you :)
Nice teaching method
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?
This is awesome, I really love this
I'm glad you like it Lucena!
Hi
Do we need to have coding knowledge to work with power query M Language please?
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
Finished watching
That's freak'n awesome! Thank you!
Glad you like it Rob!
Great!!!
Thanks!
that was awesome!! any other way to this if we don't want to use any M-Language?
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.
I wonder if it works for scane involves where the null is everywhere
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
Awesome video Chandeep
Glad you like this Chris!
Nice,, as usual.
Is "Fill up" considered as a solution in this case? I think yes....
that's a magic 🎩 excellent 👌
There are more than 5 different date columns for a line item. How to calculate age from the latest date
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’….
Hello Sandeep,
Data is mismatching from the original table
Awesome Bro❤
Really Awesome
how do I remove null values but retain the remaining values in same cells and not compress them
I want to remove null value of one column only in the table
can the transforming of column types into list can cater an infinite numbers of columns inside the table? thanks.
Pff man, this was a great trick!!! I think I am gonna implement this into my reports =)))
Awesome Mirrr!
Thank you!
Awesome tricks❤
Thanks!
Fill in up or down option would not solved this issue in the beginning?
Give it a try ;)
Great 👍
Awesome!
Incredible!
Thanks Ivan!
Thank you so much. But its not as simple as you say it is😅
After doing all this i still have null values in my data , why?
Thanks!
Thanks a lot Alex. Cheers
superb!!
Thanks Jaipal!
Brilliant.
Thanks Ron!
That is *exactly* how I would have done it.
Good stuff! :-)
Thanks Geert!
thanks you sm
Awesome
Great.....
Thanks Bulbul!
power query GOD!!!!
Thanks Domar!
Yes this is "awesome"
Glad you like it Sandeep!
Nice
Transpose - merge - transpose again.🤗
kidding of course :)
magic detected… ❤
Amazing
Thanks Al Bizri
not working 3rd step explain ?????????
👍👍👍👌
THIS never actually worked for me
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