Excel 2016 power Query, Table contains

Mythbuster 41 Reputation points
2021-09-28T13:45:55.507+00:00

Hi Excel Power Query experts,

I am trying to add the ID to the unique list when the mapping text is similar (contains contents) map the corresponding ID likewise for the rest of the table. I have an office 2016 professional.

Office 365 is only available excel online.,

Example This is the source data attached.135954-transformingdata.png

I tried using merge various ways and as I cannot use fuzzy mapping, the merge is looking for the exact match instead of contains.

I tried with Table.ContainsAny(

Table.FromRecords({   

    [a = 1, b = 2],   

    [a = 3, b = 4]   

}),   

{   

    [a = 1, b = 3],   

    [a = 3, b = 5]   

},   

"a"   

)

the above function seems right but I have no idea how to use it in a real example, and Instead of output "true", I need to check the next record till the end of the records and for every match, add IDs to the list.

My office data has 1000s of data with about 30 columns but, I might concatenate the relevant columns to get my output.
Your help is greatly appreciated.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ron Rosenfeld 271 Reputation points
    2021-09-29T00:35:09.963+00:00

    One way that might work would be to use the Table.FuzzyGroup method:

    let  
        Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],  
        typed = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Mapping String", type text}}),  
      
    //fuzzy group by Mapping String  
    //Threshold is set by trial and error  
        fuzzyGroup = Table.FuzzyGroup(typed, "Mapping String",{  
            {"Id", each List.First([Id]), Int64.Type},  
            {"Mapped IDs", each Text.Combine(  
                List.Transform(  
                    List.Sort(List.RemoveFirstN([Id]), Order.Ascending) , each Number.ToText(_)),";"),  
                        type text}  
        },  
            [IgnoreCase=true, IgnoreSpace=true, Threshold=0.5]   
        ),  
      
    //Set column orders as desired and sort by Id  
        #"Reordered Columns" = Table.ReorderColumns(fuzzyGroup,{"Id", "Mapping String", "Mapped IDs"}),  
        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Id", Order.Ascending}})  
    in  
        #"Sorted Rows"  
    

    136063-image.png

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Mythbuster 41 Reputation points
    2021-09-29T11:59:27.69+00:00

    @Ron Rosenfeld Thanks a ton. It worked on my example Now I will use this on my office data.

    Please can you explain to me the code where are looking for unique data and only duplicate data is mapped on mapping ID.

    Thanks a ton.

    0 comments No comments