Forum Discussion
How to check multiple substrings based on a filter criteria
Hey everyone. Great to finally contribute to this forum; I haven’t found a comprehensive solution to this so far. I will describe what I need, how I’m doing it so far, and how I want to automate it using power query.
- I have 3 tables as shown in the image.
- The objective is add 2 columns to Table 1 and arrive at the final table using Table 2 as an intermediate refence table.
- I need to check in table 1 if the column ‘Numbers’ has any of the values in Small Demand from table 2 for a given Team and Sprint.
- For example, in table 2, for the team ‘The Vanguard’ in sprint ‘PO.PI.2022.42’, there are 2 items in the small demand column - E-08650 and E-06340.
- If any of those two values are contained in Table 1’s Number column, then return 1, if none of those two values is found, return 0.
- Repeat these steps for the Maintenance/Sustainability column
- A graphical representation is shown below
I've been able to do it in Excel using the formula:
=SUM(--ISNUMBER(SEARCH(FILTER(Table3[[#All],[Small Demand]],(Table3[[#All],[Team]]=[@Team])*(Table3[[#All],[Value Stream PI]]=[@Sprint])),[@Numbers])))
How do I go about doing this in Power Query?
Hi Anonymous,
Something like this (I simplified your example, but it reasonbly simple to scale it back):
let t1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUMdIx1jHRMVWK1YlWcgKKQPk6ZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Numbers = _t]), t2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJSitWBsIzhLBM4yxTMcgKyDOAsCywsS6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Small = _t]), #"Merged Queries" = Table.NestedJoin(t1, {"Team"}, t2, {"Team"}, "Custom1", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Small Demand", each List.ContainsAny(Text.Split([Numbers], ","), [Custom1][Small])) in #"Added Custom"Cheers,
John
2 Replies
Hi Anonymous,
Something like this (I simplified your example, but it reasonbly simple to scale it back):
let t1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUMdIx1jHRMVWK1YlWcgKKQPk6ZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Numbers = _t]), t2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJSitWBsIzhLBM4yxTMcgKyDOAsCywsS6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Small = _t]), #"Merged Queries" = Table.NestedJoin(t1, {"Team"}, t2, {"Team"}, "Custom1", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Small Demand", each List.ContainsAny(Text.Split([Numbers], ","), [Custom1][Small])) in #"Added Custom"Cheers,
John
NewStep=let col={"Small Demand","Maintenance/Substainabilties"},RefTbl=Table.Buffer(Table.Group(Table2,{"Team","Value Stream PI"},List.Transform(col,(x)=>{x,each Table.Column(_,x)}))) in #table(Table.ColumnNames(Table1)&col,Table.ToList(Table1,each let a=RefTbl{[Team=_{0},#"Value Stream PI"=_{1}]}? ??[],b=Text.Split(_{2},","),c=List.Transform(col,each List.Count(List.Intersect({b,Record.FieldOrDefault(a,_,{})}))) in _&c)
