Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have Table A with a list of "item" values that I need to exclude from Table B. So far so good, I use:
#"Filter" = Table.SelectRows(#"Table B", each not List.Contains(#"Table A"[#"item"], [item]))
Now I need to add an additional condition to this filter, the "item" value needs to be excluded ONLY if its "date" is more recent than the corresponding "date" in Table B:
#"Table A"[#"email"] = #"Table B"[#"email"] & #"Table A"[#"date"] > #"Table B"[#"date"]
Even more, based on this condition between Table A and B, I need to exclude these items from Table C.
I cannot use Relashionships for other reasons. I tried to exclude values from Table A, then in B, but I incurr in a loop error. I tried other ways, but no luck so far. Any ideas?
Example Tables:
Table A | Table B | Table C | |||||
date | item | date | item | date | item | ||
02/01/2020 | A | 03/01/2020 | B | 01/01/2020 | A | ||
05/01/2020 | B | 01/01/2020 | D | 01/01/2020 | F | ||
04/01/2020 | C | 05/01/2020 | C | 06/01/2020 | B | ||
Solved! Go to Solution.
#"Filter" = Table.SelectRows(#"Table B", each
let
rowA = #"Table A"{[item = [item]]}?,
rowC = #"Table C"{[item = [item]]}?
in
not (rowA <> null and rowA[email] = [email] and rowA[date] > [date]) and not (rowC <> null and rowC[email] = [email] and rowC[date] > [date])
)
Only works if [item] is unique in the tables. If performance is bad add Table.AddKey(PreviousStep, {"item"}, true) to the end (or middle) or your queries
Hi @Anonymous
This is the script for Table B
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzDUNzIwMlDSUXJSitUBChkiCblAhEyRhJyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, item = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"item", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", ( a ) => Table.RowCount( Table.SelectRows( #"Table A", ( b ) => b[item] = a[item] and b[date] < a[date] ) ) = 0 )
in
#"Filtered Rows"
#"Filter" = Table.SelectRows(#"Table B", each
let
rowA = #"Table A"{[item = [item]]}?,
rowC = #"Table C"{[item = [item]]}?
in
not (rowA <> null and rowA[email] = [email] and rowA[date] > [date]) and not (rowC <> null and rowC[email] = [email] and rowC[date] > [date])
)
Only works if [item] is unique in the tables. If performance is bad add Table.AddKey(PreviousStep, {"item"}, true) to the end (or middle) or your queries
Hi @Anonymous
Please see the attached file with a solution
Thanks fro the swift reply @Mariusz !
I only have a problem opening the file: Object reference not set to an instance of an object.
Hi @Anonymous
This is the script for Table B
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzDUNzIwMlDSUXJSitUBChkiCblAhEyRhJyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, item = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"item", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", ( a ) => Table.RowCount( Table.SelectRows( #"Table A", ( b ) => b[item] = a[item] and b[date] < a[date] ) ) = 0 )
in
#"Filtered Rows"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.