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