question

Mythbuster-3695 avatar image
0 Votes"
Mythbuster-3695 asked Mythbuster-3695 answered

Excel 2016 power Query, Table contains

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.


power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RonRosenfeld-3452 avatar image
1 Vote"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

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



image.png (26.4 KiB)
image.png (26.9 KiB)
image.png (26.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Mythbuster-3695 avatar image
0 Votes"
Mythbuster-3695 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.