Forum Discussion

crossover's avatar
crossover
Advocate I
4 years ago

Merging wildcard data between two tables

Hello! I have a data merge challange that I'm looking to solve with PowerQuery. Objective is to get data from Excel and return there too (rather than Power BI), but expecting techniques to be the sam...
Need Help
Show and Tell
AlB's avatar
AlB
Community Champion
4 years ago

Hi crossover 

I believe a combination of search and sumproduct (possibly with array formulae) would do the job in Excel.

In any case, here is a possible solution in PQ. The "#Added custom" step is the relevant one. See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpKTVFILCjISS1WUIrViVYyAoqlVhSk5hVnlqUqFKQmFhWDxY2B4skZqUVFlQpJOfnFxfm5SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Comment", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(ProductT, (inner)=> Text.Contains([Comment], inner[Product] ))[Price]), Int64.Type)
in
    #"Added Custom"

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.