Excel 2016 power Query, Table contains

Question

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.

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.

Answer 1

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"  

Answer 2

@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.