Let me know in the comments if this was useful or if you have another method? You might like to check out this Power Query video with 5 great Power Query Tips th-cam.com/video/n08zD71J2K8/w-d-xo.htmlsi=j_fGLcdNwD_HOPL9&list=PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3
Nice, Wyn👍..... but You don't need an extra column. Just use Table.SelectRows. Your second (and final) step might look like this: FilteredRows = Table.SelectRows(Source, each List.Contains(Record.ToList(_), null)) Sometimes it is beneficial to use the List.ContainsAny or List.ContainsAll function.
Thank you for sharing! It can be done like this as well:- 1. Get list of values - Record.FieldValues(_) 2. Get not null count of values which should be less or equal to Column Count - List.NonNullCount(Record.FieldValues(_)) 3. Compare with Column Count to identify if row has missing values. if List.NonNullCount(Record.FieldValues(_)) < Table.ColumnCount(#"PreviousStepName") then "Missing" else "Not Missing"
Great one, now also have a better idea of diff between record and list. Maybe a future video on how to use this change from record to list (or list to record) for other purposes?
Thanks Win for your interesting tutorial that gave me a cue to try the Record.FieldValues(_) function and with = Table.SelectRows(#"Changed type", each List.Contains(Record.FieldValues(_), null)) It works, but I'm not sure . 🙂
Below code will give new column with information which all columns have data blank:- let Source = Table, AddNullColumns = Table.AddColumn(Source, "Null Columns", each let NullColumns = List.Select(Table.ColumnNames(Source), (col) => Record.Field(_, col) = null) in Text.Combine(NullColumns, ", ") ), ChangeType = Table.TransformColumnTypes(AddNullColumns, {{"Null Columns", type text}}) in ChangeType
Awesome trick. I guess, the last step, change types, it could be included in the AddColumn( ) as the last argument what could result in a lesser convulsive and much more readable code.
Let me know in the comments if this was useful or if you have another method?
You might like to check out this Power Query video with 5 great Power Query Tips th-cam.com/video/n08zD71J2K8/w-d-xo.htmlsi=j_fGLcdNwD_HOPL9&list=PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3
Nice, Wyn👍..... but You don't need an extra column. Just use Table.SelectRows. Your second (and final) step might look like this:
FilteredRows = Table.SelectRows(Source, each List.Contains(Record.ToList(_), null))
Sometimes it is beneficial to use the List.ContainsAny or List.ContainsAll function.
Cheers Bill 😀
@@AccessAnalytic You're welcome 😀
Good content, this is a great way to figure out how to create a custom function.
Thanks for taking the time to leave a kind comment Josh.
Thanks that is well explained and will be very useful.
That's great to hear. Thank you.
Very nice. Fast and easy and quick. Thanks Wyn
Thanks Cherian
Thank you for sharing!
It can be done like this as well:-
1. Get list of values - Record.FieldValues(_)
2. Get not null count of values which should be less or equal to Column Count - List.NonNullCount(Record.FieldValues(_))
3. Compare with Column Count to identify if row has missing values.
if List.NonNullCount(Record.FieldValues(_)) < Table.ColumnCount(#"PreviousStepName") then "Missing" else "Not Missing"
Interesting approach!
_ in PQ is like the @ in an Excel table, sort of. Today is a good day, I've learned something!
‘Nice
Thanks. First time I have understood how records can be used
That’s great to hear 👍🏼
As always from you, this is brilliant! Thanks for sharing
Cheers Martin, I appreciate the kind comment.
Nice solution. Thank you Wyn!!!
Thanks Iván
Awesome 😮 more such videos please 😊
I appreciate you taking the time to let me know you found it useful
Brilliant, thank you, sir!
Thank you for the kind comment 😀
Very nice tutorial.
Thank you Bhavik
Ah, this is brilliant! Thanks.
Cheers
Thanks Wyn.
No worries Gary
Thanks Wyn, very simple and straight-forward.
Cheers Chris
Great one, now also have a better idea of diff between record and list. Maybe a future video on how to use this change from record to list (or list to record) for other purposes?
Cheers Chris, I’m sure I’ll do a few more on the topic in the future.
0:47 What about transposing the data first??
What would the step be after that?
good .thanks
Cheers
Thanks Win for your interesting tutorial that gave me a cue to try the Record.FieldValues(_) function and with = Table.SelectRows(#"Changed type", each List.Contains(Record.FieldValues(_), null))
It works, but I'm not sure . 🙂
Yep, combining it all will work too. Good one.
Just used something similar to find where 6 specific columns are all null at the same time
Great !
Below code will give new column with information which all columns have data blank:-
let
Source = Table,
AddNullColumns = Table.AddColumn(Source, "Null Columns", each
let
NullColumns = List.Select(Table.ColumnNames(Source), (col) => Record.Field(_, col) = null)
in
Text.Combine(NullColumns, ", ")
),
ChangeType = Table.TransformColumnTypes(AddNullColumns, {{"Null Columns", type text}})
in
ChangeType
Nice, thanks Buddhi 😀
Awesome trick. I guess, the last step, change types, it could be included in the AddColumn( ) as the last argument what could result in a lesser convulsive and much more readable code.