Find the missing number in Sequency

[MightyRabbit]

Hi everyone, 
I am new to PowerBI, and I have one quick question, hope you guys can help.
Let's say if I have one Column that contains different Serrie F65/F66, 
In this table below, I am missing F651236, F651237 for F65 Serrie, and F662348 for F66 Serrie. 
Is there any querry that help me to figure it out, what is missing in sequence.

F651234

F651235

F651238

F651239

F662345

F662346

F662347

F662349

Thank you for your help and support 

[edhans]

Glad I was able to help. 

[MightyRabbit]

Hi @edhans ,

Thank you very much for taking your time to look into this. This works as well. 
Best regarsd, 

[MightyRabbit]

I've found this article that help me resolve the issue: 
Find the missing numbers in the sequency across ma... - Microsoft Fabric Community

But if you guys can also find an alternative solution, please shout out 🙂

[edhans · Accepted solution]

@MightyRabbit 
The solution you linked to is in DAX, and that will work. This solution is in Power Query. Here is the result:

Here is what I did:

1. Found the series base - F65, F66, etc.
2. Extracted the numerical portion - 1234 for example
3. Grouped by the series base and added all data to nested tables
4. Added a list of missing numbers by getting the min/max of each series base and creating a full list of those numbers, then removing those that already existed. So for F65 it returned a list of 1236 and 1237
5. Expanded those missing numbers and added the series base back to them, then added a column that simply said "Missing"
6. Removed unnecessary columns
7. Appended the missing numbers to the original table.

Here is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjMzNTQyNlGK1YGxTZHYFkhsSyjbDKjcFIlthsQ2R2ID1ccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Series = _t]),
    #"Added Series Base" = Table.AddColumn(Source, "Series Base", each Text.Start([Series],3), type text),
    #"Added Numbers" = Table.AddColumn(#"Added Series Base", "Numbers", each Number.From(Text.AfterDelimiter([Series], [Series Base])), Int64.Type),
    #"Grouped Rows" = 
        Table.Group(
            #"Added Numbers", 
            {"Series Base"}, 
            {
                {
                    "All Rows", each _,  
                    type table [Series=nullable text, Series Base=text, Numbers=number]
                }
            }
        ),
    #"Added Missing Numbers" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Missing Numbers", 
            each 
                let
                    varAllNumbers = {List.Min([All Rows][Numbers])..List.Max([All Rows][Numbers])},
                    varExistingNumbers = [All Rows][Numbers]
                in
                List.Difference(varAllNumbers, varExistingNumbers)
            ),
    #"Expanded Missing Numbers" = Table.ExpandListColumn(#"Added Missing Numbers", "Missing Numbers"),
    #"Added Full Series" = Table.AddColumn(#"Expanded Missing Numbers", "Full Series", each [Series Base] & Text.From([Missing Numbers])),
    #"Added Is Missing" = Table.AddColumn(#"Added Full Series", "Is Missing", each "Missing"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Is Missing",{"Full Series", "Is Missing"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Full Series", "Series"}}),
    #"Appended Query" = Table.Combine({Source, #"Renamed Columns"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Series", type text}, {"Is Missing", type text}})
in
    #"Changed Type"

 How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.