My goal is to query a folder of files (unique identifiers of files contained within col1 and col2) and create an index of which files contain a line with I in column I/R and A1 or A2 in column Info (option 1), as well files that also contain another line with R in column I/R and B in column Info (Table.Contains did not seem to like text contains being used within it so I created the column Helper R to hold that formula).
The purpose of concat info is to ensure that when evaluating for the presence of the second line the system only considers lines that have the same Col1 and Col2 values as the first line. I got a cyclical reference error when I tried to use table.contains on my query directly so I created a duplicate query (Helper Query) that is the same as my primary query, minus this below step.
When I run this step I don't get any syntax errors, but every line that meets the parameters of the first if statement comes out as an error, with the error code "[Expression.Error] We cannot convert the value false to Type Record." This error appears regardless of whether the value of Helper R is false or not, so I assume that the issue has little to do with the boolean at all. Any ideas?
= Table.AddColumn(#"Changed Type1", "Outcome", each if ([#"I/R"]= "I") and (Text.Contains([Info], "A1") or Text.Contains([Info], "A2"))
then if Table.Contains(#"HELPER QUERY",#"HELPER QUERY"[Concat Info] = "R "&[Col1]&" "&[Col2],#"HELPER QUERY"[Helper R]=true)
then "Option 1"
else "Option 2"
else "Other")
Col1 | Col2 | I/R | Info | Concat Info | Helper R |
---|---|---|---|---|---|
123456 | 10 | I | A1 | A1 123456 10 | FALSE |
123456 | 10 | I | A2 | A2 123456 10 | FALSE |
123456 | 10 | I | B | B 123456 10 | FALSE |
123456 | 20 | I | B | B 123456 20 | FALSE |
123456 | 20 | I | B | B 123456 20 | FALSE |
123456 | 20 | I | B | B 123456 20 | FALSE |
123456 | 25 | R | B | B 123456 25 | TRUE |
123456 | 30 | I | B | B 123456 30 | FALSE |
123456 | 30 | I | B | B 123456 30 | FALSE |
123456 | 30 | I | B | B 123456 30 | FALSE |
123456 | 30 | I | B | B 123456 30 | FALSE |
123456 | 30 | I | B | B 123456 30 | FALSE |
123456 | 30 | R | B | B 123456 30 | TRUE |
123456 | 30 | R | B | B 123456 30 | TRUE |
123456 | 40 | I | B | B 123456 40 | FALSE |
123456 | 40 | I | B | B 123456 40 | FALSE |
123456 | 40 | I | B | B 123456 40 | FALSE |
123456 | 40 | I | B | B 123456 40 | FALSE |
123456 | 40 | R | B | B 123456 40 | TRUE |
123456 | 40 | R | A1 | A1 123456 40 | FALSE |
123456 | 50 | I | A1 | A1 123456 50 | FALSE |
123456 | 50 | I | A2 | A2 123456 50 | FALSE |
345876 | 50 | I | A1 | A1 345876 50 | FALSE |
345876 | 50 | I | B | B 345876 50 | FALSE |
345876 | 50 | R | B | B 345876 50 | TRUE |
345876 | 50 | R | B | B 345876 50 | TRUE |
345876 | 50 | R | B | B 345876 50 | TRUE |
345876 | 60 | I | B | B 345876 60 | FALSE |
Not sure exactly what you expect for output, but here is one example of what might be what you want:
Replace the Source
line with your own data source once you understand the code
Note that I do NOT use the two helper columns in your data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRMjQAEp5A7GioFKuDVcIIh4QTqrgRtcRNgUQQprgxDvV0EcflHiziJjjMoao4LnuDsMSkKa4oNkWLYqCwhTk2HZgSTljEgygXN0MyPxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, #"I/R" = _t, Info = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"I/R", type text}, {"Info", type text}}),
//Create table of Option 1's, everything else will be Option 2
Tbl1 = Table.SelectRows(#"Changed Type", each [#"I/R"]="I" and List.Contains({"A1","A2"},[Info])),
Tbl2 = Table.SelectRows(#"Changed Type", each [#"I/R"] = "R" and [Info]="B"),
Join = Table.NestedJoin(Tbl1,{"Col1","Col2"}, Tbl2, {"Col1","Col2"},"Join",JoinKind.LeftOuter),
#"Option 1" = Table.SelectRows(Join, each not Table.IsEmpty([Join])),
#"Combine Tables" = List.Accumulate(
Table.Column(#"Option 1","Join"),
Table.RemoveColumns(#"Option 1",{"Join"}),
(s,c)=> Table.Combine({s,c})),
//Lookup the Options
#"Lookup Outcomes" = Table.NestedJoin(
#"Changed Type",{"Col1","Col2","I/R","Info"},
#"Combine Tables",{"Col1","Col2","I/R","Info"},
"Outcomes",JoinKind.LeftOuter),
#"Added Outcomes" = Table.AddColumn(#"Lookup Outcomes", "Outcome",
each if Table.IsEmpty([Outcomes])
then "Option 2" else "Option 1", type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Outcomes",{"Outcomes"})
in
#"Removed Columns"