Forum Discussion

MichaelHutchens's avatar
MichaelHutchens
Helper V
2 years ago
Solved

Finding multiple string matches from one table, in another

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 NameDepartment NameTeam Name
Finance DivisionProcurement DepartmentPayments Team
Sport DivisionRugby DepartmentMedia Team
Enterprise DivisionProjects DepartmentAgile Team
Community DivisionEngagement DepartmentFunding Team
Community DivisionEngagement DepartmentPayment Team


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

Page URLPage HTML code
www.result1.comt-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.comt-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.comt-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.comt-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.comt-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:

MatchPage URL
Procurement Departmentwww.result1.com
Finance Divisionwww.result2.com
Projects Departmentwww.result3.com
Funding Teamwww.result4.com
Payment Teamwww.result5.com
Community Divisionwww.result5.com
Enterprise Divisionwww.result3.com
Procurement Departmentwww.result4.com


If anyone could assist I'd really appreciate it!

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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"

     

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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"