Forum Discussion

torovic's avatar
torovic
Frequent Visitor
4 years ago
Solved

Detectar numeración no correlativa (Power Query)

Hola necesito saber como detectar en una columna numeros NO correlativos o faltantes y colocarlos en otra columna o tabla indicando cuales faltan.  Alguna ayuda? Columna con numeración R-547137...
  • edhans's avatar
    4 years ago

    This logic in Power Query will do it torovic 

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc+xDcAgFMTQXagTCQIf7tZIi9h/DdIFl66ePGd672ij1JHTuv4qqAdVUYHqqIHyWYIneIIneGooCBIKnuEZnuEZgvFn/Bm6ofvUWy6oigpUR33C2g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Valid", each "OK", type text),
        ExistingNumbers = List.Transform(#"Added Custom"[Data], each Number.From(Text.End(_, 7))),
        AllNumbers = {List.Min(ExistingNumbers)..List.Max(ExistingNumbers)},
        Difference = List.Difference(AllNumbers, ExistingNumbers),
        Missing = List.Transform(Difference, each "R-" & Text.From(_)),
        #"Converted to Table" = Table.FromList(Missing, Splitter.SplitByNothing(), {"Valid"}, null, ExtraValues.Error),
        #"Appended Query" = Table.Combine({#"Added Custom", #"Converted to Table"}),
        #"Added Custom1" = Table.AddColumn(#"Appended Query", "Sort Column", each if [Data] = null then Number.From(Text.End([Valid], 7)) else Number.From(Text.End([Data],7))),
        #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Sort Column", Order.Ascending}})
    in
        #"Sorted Rows"

     

    It returns this. You can delete the sort column if desired.

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.