Forum Discussion
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 departmen...
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"
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"
That's perfect, thank you so much lbendlin . You've saved me quite a few hours of manual work! 🙂
