How to Identify Rows with Missing Values using Power Query

[@AccessAnalytic]

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 ua-cam.com/video/n08zD71J2K8/v-deo.htmlsi=j_fGLcdNwD_HOPL9&list=PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3

[@BillSzysz1]

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.

[@AccessAnalytic]

Cheers Bill 😀

[@BillSzysz1]

@@AccessAnalytic You're welcome 😀

[@josh_excel]

Good content, this is a great way to figure out how to create a custom function.

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment Josh.

[@BlockFHatter]

Thanks that is well explained and will be very useful.

[@AccessAnalytic]

That's great to hear. Thank you.

[@cherianiype]

Very nice. Fast and easy and quick. Thanks Wyn

[@AccessAnalytic]

Thanks Cherian

[@buddhigupta3557]

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"

[@AccessAnalytic]

Interesting approach!

[@sledgehammer-productions]

_ in PQ is like the @ in an Excel table, sort of. Today is a good day, I've learned something!

[@AccessAnalytic]

‘Nice

[@garylhaas2005]

Thanks. First time I have understood how records can be used

[@AccessAnalytic]

That’s great to hear 👍🏼

[@IvanCortinas_ES]

Nice solution. Thank you Wyn!!!

[@AccessAnalytic]

Thanks Iván

[@moc61]

As always from you, this is brilliant! Thanks for sharing

[@AccessAnalytic]

Cheers Martin, I appreciate the kind comment.

[@Sumanth1601]

Awesome 😮 more such videos please 😊

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful

[@SamFisher-x2y]

Brilliant, thank you, sir!

[@AccessAnalytic]

Thank you for the kind comment 😀

[@Bhavik_Khatri]

Very nice tutorial.

[@AccessAnalytic]

Thank you Bhavik

[@JJ_TheGreat]

Ah, this is brilliant! Thanks.

[@AccessAnalytic]

Cheers

[@garyflagray1092]

Thanks Wyn.

[@AccessAnalytic]

No worries Gary

[@chrism9037]

Thanks Wyn, very simple and straight-forward.

[@AccessAnalytic]

Cheers Chris

[@ExcelWithChris]

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?

[@AccessAnalytic]

Cheers Chris, I’m sure I’ll do a few more on the topic in the future.

[@JJ_TheGreat]

0:47 What about transposing the data first??

[@AccessAnalytic]

What would the step be after that?

[@flaviogarlatticosta]

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 . 🙂

[@AccessAnalytic]

Yep, combining it all will work too. Good one.

[@putta231178]

good .thanks

[@AccessAnalytic]

Cheers

[@alexbarbucristi]

Just used something similar to find where 6 specific columns are all null at the same time

[@AccessAnalytic]

Great !

[@buddhigupta3557]

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

[@AccessAnalytic]

Nice, thanks Buddhi 😀

[@osoriomatucurane9511]

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.