Check for missing values in a listing and change them

[Isidro]

Hello,

I would like to know if you can create a query where you compare a column with a list of unique values of accounting accounts with another column with repeated values of accounting accounts to check which are not part of the list of unique values and those that are not part Change to the closest value in the list of unique values.

Thank you very much and greetings

[MarcelBeug]

This is my interpretation of your question.

The first table (Table1) is converted to the second table with the query code below.

let
    Source = Table1,
    Splitted = Table.TransformColumns(Source,{{"Repeated", each List.Transform(Text.Split(_,","), Number.From)}}),
    Expanded = Table.ExpandListColumn(Splitted, "Repeated"),
    AddedClosest = Table.AddColumn(Expanded, "Closest", (This) => List.Min(List.Transform(Splitted[Unique], each {_,Number.Abs(_-This[Repeated])}),null,each _{1}){0}),
    RemovedRepeated = Table.RemoveColumns(AddedClosest,{"Repeated"}),
    ClosestToText = Table.TransformColumnTypes(RemovedRepeated,{{"Closest", type text}}),
    Grouped = Table.Group(ClosestToText, {"Unique"}, {{"NewRepeated", each Text.Combine([Closest],","), type text}})
in
    Grouped

I first created a query with connection only for Table1, took that as the source for the query above, splitted and expanded the column with repeated values, added a column with the closest value from the unique list (by transforming the list of unique values to a list of lists with the unique value and the difference between the unique value and the repeated value, determine the minimum difference {1} and return the associated unique value {0}), then some finsihing touches to combine the results back to repeated lists with comma separated values.

This video illustrates how it looks like when going through the query step by step:

[Isidro]

Hello MarcelBeug,

Thanks for your contribution. What I want to do is validate data from a data list. I have a list of unique values of 500 values, for example, and I create a query where one of the columns has 2,500 values, for example, repeated and what I want is to check those values of that list that are not in the list of unique values and change it by a value, as close as possible.I want the query to check me for each of the accounts, for example, 46500000 does not exist in the list of unique values and the one that exists is 462000000 and what I want is to change all 46500000 in that column by the 462000000.

Another question is how you can include that unique list of about 500 values in a query with more than 2,000 rows.

Thank you very much and greetings.

[MarcelBeug · Accepted solution]

If I translate my code to your latest information, then:

- your table with repeated values is step Expanded

- your column with repeated values is Repeated

- your list with unique values is Splitted[Unique]

so you need to substitute these in the step AddedClosest..

With regard to your second question, I created another query with he code below.

let
    Source = Table1,
    Splitted = Table.TransformColumns(Source,{{"Repeated", each List.Transform(Text.Split(_,","), Number.From)}}),
    Expanded = Table.ExpandListColumn(Splitted, "Repeated"),
    Records = Table.ToRecords(Expanded),
    TableFromColumns = Table.FromColumns({Records,Splitted[Unique]}),
    ExpandedRecords = Table.ExpandRecordColumn(TableFromColumns, "Column1", {"Unique", "Repeated"}, {"Unique", "Repeated"}),
    Removed = Table.RemoveColumns(ExpandedRecords,{"Unique"}),
    Renamed = Table.RenameColumns(Removed,{{"Column2", "Unique"}})
in
    Renamed

A step by step walkthrough:

[Isidro]

Hello Marcel,

Thank you for the information.

Best regards.

[vanessafvg]

nice music @MarcelBeug