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 a table A with column IDA1 (it is of type text) and VALA like this :
IDA1 VALA
tredsj:43294545396645096,gfrgre:56657443676654 whatever1
null whatever2
5434556657445457 whatever3
sdfjlfjregjgjgkm:63745734764767657 whatever4
and a Table B with column IDB1 (it is of type Int64) and IDB2 (it is of type Int64) and VALB like this :
IDB1 IDB2 VALB
643693737696369346 3465556433465636 rewhatever1
43294545396645096 5432656564567742 rewhatever2
63745734764767657 63364563474354 rewhatever3
Here is the source code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYyxDgIxDEP/JXMHuDiJ2l+pOiBdWlRg6Z3g96lODIfswbKenTPtw9etJ/ASIRCOqpBL1NDqaMOTqIoBrDYDKNDnftv97eNKJWQ6FctRCBjyG80/OwF8ANta+7P24a1PPV5J2SbHMJ02/ZuASvkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDA1 = _t, VALA = _t]),
A = Table.TransformColumnTypes(Source,{{"IDA1", type text}, {"VALA", type text}}),
SourceB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYs7CgMxDESvElRvkXg+wmcxLlIYUi8huX7kdAtCDG/mjREm3JFId+9ExxH1JVW1Q+FC5/q+nu/1Wecj5jGCaJ2i0G3q3vdGBWtfopzJdtHaXzOSSjBdl1beamRgK0UJ8WIh5vwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDB1 = _t, IDB2 = _t, VALB = _t]),
B = Table.TransformColumnTypes(SourceB,{{"IDB1", Int64.Type}, {"IDB2", Int64.Type}, {"VALB", type text}})
in
B
I need to get all the lines where IDB1 or IDB2 in table B is contained in IDA1 of table A and I have no clue how to do this join in Power Query, could you enlighten me please ?
Thanks
Hi @Anonymous
yes, it's been a while and this is quite a complex topic that would require more efford from me than I am currently able to invest.
So if no one else jumps into this thread, I'd recommend you open a new one for it.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
not sure which table should be filtered, so I included both options:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"VYyxDgIxDEP/JXMHuDiJ2l+pOiBdWlRg6Z3g96lODIfswbKenTPtw9etJ/ASIRCOqpBL1NDqaMOTqIoBrDYDKNDnftv97eNKJWQ6FctRCBjyG80/OwF8ANta+7P24a1PPV5J2SbHMJ02/ZuASvkC",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [IDA1 = _t, VALA = _t]
),
A = Table.TransformColumnTypes(Source, {{"IDA1", type text}, {"VALA", type text}}),
SourceB = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"VYs7CgMxDESvElRvkXg+wmcxLlIYUi8huX7kdAtCDG/mjREm3JFId+9ExxH1JVW1Q+FC5/q+nu/1Wecj5jGCaJ2i0G3q3vdGBWtfopzJdtHaXzOSSjBdl1beamRgK0UJ8WIh5vwB",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [IDB1 = _t, IDB2 = _t, VALB = _t]
),
B = Table.TransformColumnTypes(SourceB, {{"IDB1", Int64.Type}, {"IDB2", Int64.Type}, {"VALB", type text}}),
FilterA = Table.AddColumn(
A,
"ContainsB",
each List.AnyTrue(List.Transform(B[IDB1] & B[IDB2], (l) => Text.Contains([IDA1], Text.From(l))))
),
FilterB = Table.AddColumn(
B,
"ContainsA",
each Table.SelectRows(A, (A) => Text.Contains(A[IDA1], Text.From([IDB1])) or Text.Contains(A[IDA1], Text.From([IDB2])))
),
#"Expanded ContainsA" = Table.ExpandTableColumn(FilterB, "ContainsA", {"VALA"}, {"VALA"})
in
#"Expanded ContainsA"
However, for larger tables this will be an expensive operation. To speed it up, I would recommend buffers that I have included in the file attached.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF
I finally had time to use your answer. It seems very close to what I need.
FilterA is a nice filter but it doesn't JOIN the 2 tables so I've left that out.
Regarding FilterB, it works but not for cases where my ID fields are null (In the sample I gave, IDA1 can have null values but IDB1 and IDB2 can actually have null values too).
When calling the step "Expanded ContainsA", it stops when there is a line with Error (I actually don't understand why ExpandTableColumn() has this behaviour instead of giving at least all the lines where there is no Error by the way, do you know ?).
As a not so nice workaround I tried modifying the step FilterB like this (I know "ABCDEFGHIJKLMNOPQRSTUVWXYZ" is never present in neither IDB1 nor IDB2, so if my ID is null then I use this value) :
= Table.AddColumn(
B,
"ContainsA",
each Table.SelectRows(A, (A) => Text.Contains(A[IDA1], if Text.From(IDB1) = null then "ABCDEFGHIJKLMNOPQRSTUVWXYZ" else Text.From(IDB1)) or Text.Contains(A[IDA1], if Text.From([IDB2]) = null then "ABCDEFGHIJKLMNOPQRSTUVWXYZ" else Text.From([IIDB2])))
)
I don't have an error anymore while expanding but I end up with wrong data. On the lines where there was Error before, now my column ContainsA contains a non-empty list, whereas it should be empty. Do you know why there is a non-empty list and more importantly, do you know how to adjust your answer to handle null values ?
When the value of an ID column is null, this column shouldn't return any match.
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.