This is extremely helpful with export data which might or might not have columns which contain data and prevent 'missing' data because it is suddenly added while using a manual select columns. Thanks!
Really helpful - thanks. Particularly useful when inheriting upstream source data that is not under your control. 350 columns down to 36 useful ones in one easy step.
that's so nice to hear!! thx a lot for watching all the videos, really appreciate it 😀 .. oh man, 100k still seems very far away, but I hope you are right haha 😉
Here is a simple method ,which I tried on your data set, and it worked!! I used List.Sum to check if a row is returning ONLY null value & filtered it out. After transposing the table, add a custom column : Table.AddColumn(#"Transposed Table", "Check for Null Row", each List.Sum({[Column2],[Column3],[Column4]})) Now an error would be obtained for the date row (1st row in this case). Replace error with a zero value. Filter out rows that are null in the new column. Transpose Promote headers Filter OUT the zero value to ensure Date column does not have an unwanted record or row. Change Type to ensures dates show up as dates and not as numbers Thats it !!
Hi @Ravi Thanks for sharing your solution, did you test this method with large data, how much refresh time it takes as compared to small data, have you used QUERY DIAGNOSTICS to check for the times that this step takes against large datasets?
Hi Ravi, thx for sharing! 🙂 what is the advantage over just filtering out the null / blank rows straight away?... like this you need to know in advance the names of the columns,.. 2 - the transpose option is slow
Expanding on your example. Let's say I want to keep only column(s) that have 75% or more of non-null (out of 1000), so instead of 0, I guess I can do >= 750? Also, it would be better if I can express it as 75%, instead of total number of rows as 750 above. Any ideas is appreciated. Thanks and keep up the good work.
hi Bas. Thank you for your excellent video. I have a query about the Option 1 method which use transposing. In your video 1'47'', you filtered out the null value by column 2. Suppose the call centre for 10/06/2021 is non null, the call centre will be still removed just because Column2 is null. I do not feel you remove the row just using one column (Column2 in this case) is a reliable approach. Please kindly test by adding call cente to for date 10/06/2021, you will that cost centre column is removed although the call centre column is not all null. Thank you.
Question: I have a use case similar to this… but I’ve larger dataset 250 columns and lots of data. Out of which 100 columns are placed on matrix visualization. The visualization should show only columns which has data, otherwise hide the empty columns for selected date and Account. Could you please suggest what is the better approach
we can fix that 😄 - try this = List.Select( Table.ColumnNames(#"Replaced Value"), // each List.NonNullCount( Table.Column(Data_Table, _) ) 0 each List.Count( List.Select( Table.Column(#"Replaced Value", _), each _ "" ) ) 0 )
very nice video. If there are many random null, Let's say- I imported a query from PDF. There are so many random Null cells in power query. If I want to remove all Null cells and move data to left side(just like excel- "Delete cell--> "Shift cells left"), how can i do this? Thanks
Great explanation Bass. I have a problem with my data, I still have no header set, numerous columns without any data, text number in columns without typing, when I try to perform the method that taught, I get the following error: ([DataFormat.Error] Invalid cell value '#NUM!'.) Could you guide me please, I'm lost in this case.
Thanks for the video. Well explained. May I know if it works if the data_table contains more than 1000 rows? What I mean is, if the first non-null item falls beyondthe first 1000 rows, would that column be removed?
I love this but there is a problem when it comes to my dataset. The SF object that is my table has to be filtered by a field first. How do I put this piece into the mix?
Hi Sir, is this possible? In power bi, i created a matrix visual with rows division, group, category, class, subclass and brand, now I have a supplier slicer, now I want that once I filter a certain supplier, I want the division, group, category, class, subclass to be filtered only for those that has that supplier but I want to show all the brands from the filtered division, group, categories, class and subclass whether those brand is from the supplier or not. The name of the table is 'item Details'. The matrix visual to be used is only 1.
This is super helpful but I wish you would show more of the computer screen and less of your person. I'm a little newer to things like this and I'm getting a little mixed up what you are doing where... I want to see your tables and your applied steps together. Some better table naming might help too....I'm confused if the Data_Table reference is referencing your Table called Data or is a typical function. I'll have to play some more to figure out how to use this myself but Thanks for the guidelines.
This is a power query solution to remove empty columns (so the part that gets the data into the right shape). Slicers have nothing to do with this. It sounds to me that you have separate columns for different (product/customer) categories. You probably need to unpivot those columns first (turn category columns into rows) - so that you have 1 column "category" with all categories and 1 column with the corresponding values.
I found another way in web (tbl) => let Headers = Table.ColumnNames(tbl), Result = Table.SelectColumns( tbl, List.Select(Headers, each List.MatchesAny(Table.Column(tbl, _), each _ null))) in Result
You, Bas, are a Gentleman and a Scholar!
I salute you!
This is extremely helpful with export data which might or might not have columns which contain data and prevent 'missing' data because it is suddenly added while using a manual select columns. Thanks!
you're a genius guy ! Always a pleasure to learn with you :-)
Great. Just what I need right now! So well explained for us lesser mortals. Thanks a lot.
Really helpful - thanks. Particularly useful when inheriting upstream source data that is not under your control. 350 columns down to 36 useful ones in one easy step.
I am great fan of yours. I strongly believe u will have 100K subscribers very soon
that's so nice to hear!! thx a lot for watching all the videos, really appreciate it 😀 .. oh man, 100k still seems very far away, but I hope you are right haha 😉
I saw another video similar to this but your solution is way better! Thanks, you're a legend!
thanks a lot!
Well Buzz, great video. A great trick and great explanation! Love your videos. Grettings from Colombia.
Thx Rafael for watching again!!! greetings from Germany 😉
You're awesome! Please keep doing these - you're much better than AI!
Man, really appreciate your effort on this video, I has been very helpful
That's brilliant! Sooo good. Excellent video, thank-you Bas!
You are welcome! Glad you like it :)
Here is a simple method ,which I tried on your data set, and it worked!!
I used List.Sum to check if a row is returning ONLY null value & filtered it out.
After transposing the table, add a custom column :
Table.AddColumn(#"Transposed Table", "Check for Null Row", each List.Sum({[Column2],[Column3],[Column4]}))
Now an error would be obtained for the date row (1st row in this case).
Replace error with a zero value.
Filter out rows that are null in the new column.
Transpose
Promote headers
Filter OUT the zero value to ensure Date column does not have an unwanted record or row.
Change Type to ensures dates show up as dates and not as numbers
Thats it !!
Hi @Ravi
Thanks for sharing your solution, did you test this method with large data, how much refresh time it takes as compared to small data, have you used QUERY DIAGNOSTICS to check for the times that this step takes against large datasets?
Hi Ravi, thx for sharing! 🙂
what is the advantage over just filtering out the null / blank rows straight away?... like this you need to know in advance the names of the columns,.. 2 - the transpose option is slow
Brilliant! Really well done !!
Thanks a lot Daniel! 😀
Great technique. Extremely well explained. Thanks, Bas!
Thanks a lot Ken!
this guy is just amazing! you helped me a lot man!!
Super happy to hear this Anouar!
You are a master in m language, more vidéo please
definitely more to come 😉😀
Very good! 👏👏👏 Thanks for sharing.
thx Cesar!!! 🙂
Very simple and nice smile)
Marvellous! Extremely helpful!!
Glad it was helpful!
That's a great tip, very helpful. Thank you so much. I used it in Excel Power Query.
nice 😀 happy to hear it helped you
Really helpful - Thank you!!
Awesome! Definitely going to use it soon!
Expanding on your example. Let's say I want to keep only column(s) that have 75% or more of non-null (out of 1000), so instead of 0, I guess I can do >= 750?
Also, it would be better if I can express it as 75%, instead of total number of rows as 750 above.
Any ideas is appreciated. Thanks and keep up the good work.
Thank you so much! :)
Very Good!
Thank you Bas! So cool... ;)
happy you liked it Begin! 😀
Nicely done sir.
Super useful tutorial. Thanks :-)
Happy to hear! 😀
Awesome video! the step by step creation/ explanation of the code was top tier. Thank u Bas
thank you so much Stefanos! that's really nice to hear!
hi Bas. Thank you for your excellent video. I have a query about the Option 1 method which use transposing. In your video 1'47'', you filtered out the null value by column 2. Suppose the call centre for 10/06/2021 is non null, the call centre will be still removed just because Column2 is null. I do not feel you remove the row just using one column (Column2 in this case) is a reliable approach. Please kindly test by adding call cente to for date 10/06/2021, you will that cost centre column is removed although the call centre column is not all null. Thank you.
I want to either hide or show columns depending on having value or not, but on table visual itself...any idea Will be really appreciated!
Question: I have a use case similar to this… but I’ve larger dataset 250 columns and lots of data. Out of which 100 columns are placed on matrix visualization. The visualization should show only columns which has data, otherwise hide the empty columns for selected date and Account. Could you please suggest what is the better approach
Thanks! I have to add that it only works if the cells are "null", and not if they are ""
we can fix that 😄 - try this
= List.Select(
Table.ColumnNames(#"Replaced Value"),
// each List.NonNullCount( Table.Column(Data_Table, _) ) 0
each List.Count( List.Select( Table.Column(#"Replaced Value", _), each _ "" ) ) 0
)
fabulous👍👍👍👍
Thx Kebin!
this is a great a video.. but kind of confusing with underscore "_" mark??
very nice video. If there are many random null, Let's say- I imported a query from PDF. There are so many random Null cells in power query. If I want to remove all Null cells and move data to left side(just like excel- "Delete cell--> "Shift cells left"), how can i do this? Thanks
Great explanation Bass. I have a problem with my data, I still have no header set, numerous columns without any data, text number in columns without typing, when I try to perform the method that taught, I get the following error: ([DataFormat.Error] Invalid cell value '#NUM!'.) Could you guide me please, I'm lost in this case.
Thanks for the video. Well explained.
May I know if it works if the data_table contains more than 1000 rows? What I mean is, if the first non-null item falls beyondthe first 1000 rows, would that column be removed?
you are great!!!!!!!! god!!!
Thanks! Happy you like it!
How can we apply this to a PowerBI Table, like remove all null columns in it
Can we dynamically remove duplicate rows? Can you make a tutorial on this too?
I love this but there is a problem when it comes to my dataset. The SF object that is my table has to be filtered by a field first. How do I put this piece into the mix?
Hi Sir, is this possible? In power bi, i created a matrix visual with rows division, group, category, class, subclass and brand, now I have a supplier slicer, now I want that once I filter a certain supplier, I want the division, group, category, class, subclass to be filtered only for those that has that supplier but I want to show all the brands from the filtered division, group, categories, class and subclass whether those brand is from the supplier or not. The name of the table is 'item Details'. The matrix visual to be used is only 1.
Just fell in love with you :D
This is super helpful but I wish you would show more of the computer screen and less of your person. I'm a little newer to things like this and I'm getting a little mixed up what you are doing where... I want to see your tables and your applied steps together. Some better table naming might help too....I'm confused if the Data_Table reference is referencing your Table called Data or is a typical function. I'll have to play some more to figure out how to use this myself but Thanks for the guidelines.
Hi Bas I want to automatically remove columns with value 0 in my pbi query.
do you have a tip/solution for me?
Great example. But what if columns are empty instead of "null"?
you can simply adjust the filter condition 🙂
= List.Select(
Table.ColumnNames(#"Replaced Value"),
// each List.NonNullCount( Table.Column(Data_Table, _) ) 0
each List.Count( List.Select( Table.Column(#"Replaced Value", _), each _ "" ) ) 0
)
Can we do this as well when doing filtering in slicer ? in case of some categories have null when not included in the filtering
I tried this to my data, but when I do filtering for a category in the slicer. Then the result keep showing row contain null column
This is a power query solution to remove empty columns (so the part that gets the data into the right shape). Slicers have nothing to do with this. It sounds to me that you have separate columns for different (product/customer) categories. You probably need to unpivot those columns first (turn category columns into rows) - so that you have 1 column "category" with all categories and 1 column with the corresponding values.
I found another way in web
(tbl) =>
let
Headers = Table.ColumnNames(tbl),
Result = Table.SelectColumns(
tbl,
List.Select(Headers, each List.MatchesAny(Table.Column(tbl, _), each _ null)))
in
Result
its really good ,but not worked with me
Earlier this method works but now it's not working 😭😭😭😭
What if I've got 300,000 lines. It will crash the data.
don't see why - option 2 works very well for me with larger data sets
@@HowtoPowerBI ok will try it again. Is there a way to remove columns with " _1", "_2", and so fourth with numbers?