Forum Discussion
Detectar numeración no correlativa (Power Query)
- 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.
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.