Find Missing Values

[PiyushBQ]

Hi Guys,

I'm trying to identify missing cells in between a series, while ignoring empty fields that don't belong to any series.

Basically, if an empty cell has values filled in adjacent fields, it should get detected. Whereas, empty cells that don't have values in adjacent fields should get ignored.



The source file.

I have to solve this using the Query Editor.

Any ideas on how to go on about it?

Thanks!

[v-shex-msft · Accepted solution]

Hi @PiyushBQ,

You can take a look at the following formula to create a custom column to store all missed fields:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVBJDgMhDPsL5x7iLIQc2z5jNP//RomZWyUUyxDbCdc13uM1IEt0Y4Uv0iRdBt+QS6QZ1BpkZUNVtALBN/PnMqm3MKLUbIyQakRQtOHpQ/m4X9f4bPZ/MHsKpD+0nBW7KtpQORFmmyqjtISvQtsvQ5O7JFjj7NdOZU08GILFSZ8MTRorv2OangGmd084Tgs3jiNzG/f9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Q3 2014" = _t, #"Q4 2014" = _t, #"Q1 2015" = _t, #"Q2 2015" = _t, #"Q3 2015" = _t, #"Q4 2015" = _t, #"Q1 2016" = _t, #"Q2 2016" = _t, #"Q3 2016" = _t, #"Q4 2016" = _t, #"Q1 2017" = _t, #"Q2 2017" = _t, #"Q3 2017" = _t, #"Q4 2017" = _t, #"Q1 2018" = _t, #"Q2 2018" = _t, #"Q3 2018" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Q3 2014", Int64.Type}, {"Q4 2014", Int64.Type}, {"Q1 2015", Int64.Type}, {"Q2 2015", Int64.Type}, {"Q3 2015", Int64.Type}, {"Q4 2015", Int64.Type}, {"Q1 2016", Int64.Type}, {"Q2 2016", Int64.Type}, {"Q3 2016", Int64.Type}, {"Q4 2016", Int64.Type}, {"Q1 2017", Int64.Type}, {"Q2 2017", Int64.Type}, {"Q3 2017", Int64.Type}, {"Q4 2017", Int64.Type}, {"Q1 2018", Int64.Type}, {"Q2 2018", Int64.Type}, {"Q3 2018", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Missed", each List.Combine(Table.SelectRows(Table.Group(Table.AddColumn(Table.FromList(Table.SelectRows(Record.ToTable(_),each [Value]=null)[Name]),"Year",each Text.End([Column1],4)), {"Year"}, {{"Contents", each _[Column1], type list}}),each List.Count([Contents])<4)[Contents])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Missed", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

Regards,

Xiaoxin Sheng