Need help to find gaps in years

[lachelleb]

New to PBI, I've got a list of years in some data (think a list of movies and the years they were produced). I'm trying to find a way to display the missing years. 

I've got them displayed using a bar chart, but I'm trying to find a way to do a count of years even though they aren't there. if that makes sense. 

Think like years

1926

1930

1931

1933

1934

1935

1936

So I want to display the missing years 1927, 1928, 1929, 1932 as blanks. If this is possible. 

I'm used to using ACL (Audit Command Language) and using a function called gaps) that just presents the gaps in the data. Whereas I don't see a function like that here. Or in Excel using v-lookup to find the missing years. So confused on how to do this here. 

[lachelleb]

Thank you @ronrsnfld , This worked perfectly to what I was wanting to see happen. 

[ronrsnfld · Accepted solution]

Little hard to know exactly what you want, but the following code will generate a List of all the years, with blanks for the missing years.

Algorithm should be obvious from the code. If not, ask.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0MlOK1QExjA1gDEMYwxjGMIExTGEMoK5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Years = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Years", Int64.Type}}),
    #"Count of Years" = List.Max(#"Changed Type"[Years]) - List.Min(#"Changed Type"[Years])+1,
    
    #"Display Missing Years as Blanks" = 
        let 
            #"All Years" = List.Numbers(List.Min(#"Changed Type"[Years]), #"Count of Years"),
            #"Missing Years" = List.RemoveMatchingItems(#"All Years", #"Changed Type"[Years]),
            #"Blanks" = List.ReplaceMatchingItems(#"All Years",List.Zip({#"Missing Years", List.Repeat({null}, List.Count(#"Missing Years"))}))
        in 
            #"Blanks"
in
    #"Display Missing Years as Blanks"

Original

Results