Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Isidro
Helper IV
Helper IV

Check for missing values in a listing and change them

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

1 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:

 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

This is my interpretation of your question.

 

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

 

Check for missing values in a listing and change them.png

 

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:

Specializing in Power Query Formula Language (M)

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.Respuesta check.jpgI 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.

 

 

 

 

 

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:

 

Specializing in Power Query Formula Language (M)

Hello Marcel,

 

Thank you for the information.

 

Best regards.

nice music @MarcelBeug





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors