Finding multiple string matches from one table, in another

[MichaelHutchens]

Hi everyone, I'm hoping someone might be able to help!

I have two spread sheets - 'A' one with a list of names, and 'B' with details from some intranet pages.

Spreadsheet 'A' contains department and division names. Spreadsheet 'B' contains two columns - the first column is a list of page URLs from our intranet, and the second column is the HTML code for each page.

I need to find the intranet page URLs in spread sheet 'B' that contain the department and division names in spread sheet 'A'.

Our organisation is going through a restructure, which means some of the department and division names will be changing, so I need to find where they are on our pages.

There may be more than one Team per department, or more than one department per division.

There may be more than one URL returned for each name in spread sheet 'A'.

This is a 'many' to 'many'-type search.

Here's an example of spread sheet 'A':

Division Name	Department Name	Team Name
Finance Division	Procurement Department	Payments Team
Sport Division	Rugby Department	Media Team
Enterprise Division	Projects Department	Agile Team
Community Division	Engagement Department	Funding Team
Community Division	Engagement Department	Payment Team

And here is an example of spread sheet 'B':

Page URL	Page HTML code
www.result1.com	t-listId="cf845d93-9f77-4554-bb97-2213075a121d" data-sp-cust-uniqueId="&#123;EF3A709E-2364-4B9D-8C84-58341D2A534D&#125;" data-sp-cust-minCanvasWidth="1" data-sp-cust-fixedWidth="100" /><img data-sp-prop-name="items[4].image.url" src="Procurement Department" data-sp-cust-siteId="e07793cf-f36e-46bb-ab0e-aac9a1eb0c20" data-sp-cust-we
www.result2.com	t-listId="cf845d93-9f77-4554-bb97-2213075a121d" data-sp-cust-uniqueId="&#123;EF3A709E-2364-4B9D-8C84-58341D2A534D&#125;" data-sp-cust-minCanvasWidth="1" data-sp-cust-fixedWidth="100" /><img data-sp-prop-name="items[4].image.url" src="Finance Division" data-sp-cust-siteId="e07793cf-f36e-46bb-ab0e-aac9a1eb0c20" data-sp-
www.result3.com	t-listId="cf845d93-9f77-4554-bb97-2213075a121d" data-sp-cust-uniqueId="&#123;EF3A709E-2364-4B9D-8C84-58341D2A534D&#125;" data-sp-cust-minCanvasWidth="1" data-sp-cust-fixedWidth="100" /><img data-sp-prop-name="items[4].image.url" src="Projects Department" data-sp-cust-siteId="e07793cf-f36e-46bb-ab0e-aac9a1eb0c20" Enterprise Division" data-sp-cust-siteId="e07793cf-f36e-46bb-ab0e-aac9a1eb0c20" data-sp-cust-webId="c1e8df81-6292-4d8b-ac9b-43597f2e54a2" data-sp-cust-listId="cf845d93-9f77-4554-bb97-2213075a121d" data-sp-cust-uniqueId="ef3a70
www.result4.com	t-listId="cf845d93-9f77-4554-bb97-2213075a121d" data-sp-cust-uniqueId="&#123;EF3A709E-2364-4B9D-8C84-58341D2A534D&#125;" data-sp-cust-minCanvasWidth="1" data-sp-cust-fixedWidth="100" /><img data-sp-prop-name="items[4].image.url" src="Funding Team" data-sp-cust-siteId="e07793cf-f36e-46bb-ab0e-aac9a1eb0c20" Procurement Department"c1e8df81-6292-4d8b-ac9
www.result5.com	t-listId="cf845d93-9f77-4554-bb97-2213075a121d" data-sp-cust-uniqueId="&#123;EF3A709E-2364-4B9D-8C84-58341D2A534D&#125;" data-sp-cust-minCanvasWidth="1" data-sp-cust-fixedWidth="100" /><img data-sp-prop-name="items[4].image.url" src="Payment Team" data-sp-cust-siteId="e07793cf-f36e-46bb-ab0e-aac9a1eb0c20"Community Division"edWidth="100" /><img data

And here are the results I'd expect to see:

Match	Page URL
Procurement Department	www.result1.com
Finance Division	www.result2.com
Projects Department	www.result3.com
Funding Team	www.result4.com
Payment Team	www.result5.com
Community Division	www.result5.com
Enterprise Division	www.result3.com
Procurement Department	www.result4.com

If anyone could assist I'd really appreciate it!

[lbendlin · Accepted solution]

Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZRfa9swFMW/ivFeq0zWH9ui7KGLUzbooIyOPiR5kKXrTCOSPUtu1m8/NS1h1TYYzI95MYJ7z7V8fud6vc5v5Q6yL59v8ovn44e7TzeZ6jXk24t1fjgcFiP4aR+KheptbApob3z4qN9tctXVjGtBkeiqCjHOGWpbUSFCCoorLgtS6E2eaRkk8gNSkw9ocub7BEf5ZsKYlG8KQi9X1/SqwmKFCC0ZYu9Fg+plzRCvKSsacsUpa07t/DIdao1bSvcg/b3R4WscXaQdnfkB+lTFONbfPg2kcHwqY3cnwTD2A3LSQmw1Aaxfs+3C2GjOYhr3UelHFUu3Y6+mESy4kDUwyDE8HdMX+zjh+LWAq0pQ1aGOloBY2bZIthiQlErIAlqsCE7FhxQCOUN4DeHaOOkUZI15MN70bi77E9/p2fffwv8NVPAzJn/lAozDaPz8NF+WqX0GV0Ctu7pAJREEMV1HrRItYpSLqiPAmSSpdi7q0FFZ4SRc7ByuZKknp43bZXcg7f9G4G9/6T+nICHDz2SStZePRydnILPsrY3GhMdftv2frptvtz8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Search

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/BCsIwDAbgVyk97yXEbTdB1FvZIXahRGxa0lbo22thsE08efshfH8SY/RIDGxR9fSiRIF1p88SbBH0yFn1GEFyi20AtaWkbgheT53R1xgkb+2luHvdqxPOBCsZOKNEofS984H2U72jB0dPXOkxeF+Yct3KgR24H8eOhWdi96deXl309AY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Division Name" = _t, #"Department Name" = _t, #"Team Name" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Match"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", (k)=> Table.SelectRows(Data, each Text.Contains([Page HTML code],k[Match]))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Page URL"}, {"Page URL"})
in
    #"Expanded Custom"

[MichaelHutchens]

That's perfect, thank you so much @lbendlin . You've saved me quite a few hours of manual work! 🙂