Forum Discussion
Multi Column Relationships in the same table
Hi All, First post, I have looked for a previous answer on this and havent found something that fits. I cant have multiple active relationships on the same table to multiple columns. I am tryin...
try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3LCQAxCER78ZyL31qSlfTfxo6ysCGDhyc+nExiGqTC5gGo2SNJAE8HMDsAFi2tBMW6Oqfw/SnBTuH/XycHiF6NcRWFeZ/2Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UID = _t, A = _t, B = _t, C = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"UID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}}), AddIDCol = Table.AddColumn( ChangedType, "ID", each if [C] <> "" then [C] else if [B] <> "" then [B] else [A], type text ) in AddIDCol
Hi Anonymous
with Power Query you can move the keys all to the same column and then build the relationship
Thanks for your reply.
As in combine them into delimited single column and then search in there? Could you give me an example I am strugling with both the syntax and how to search for a match using a column as a source.
can you post your sample table ?
Table A TableB UID Key UID A B C ID 1 123456 1 321456 321456 2 654321 2 ZZZZZZ XXXXXX 123456 123456 3 123645 3 YYYYYY XXXXXX 321456 321456 4 321456 4 YYYYYY 321456 321456 5 231456 5 231456 231456 6 456321 6 XXXXXX 123645 123645 try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3LCQAxCER78ZyL31qSlfTfxo6ysCGDhyc+nExiGqTC5gGo2SNJAE8HMDsAFi2tBMW6Oqfw/SnBTuH/XycHiF6NcRWFeZ/2Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UID = _t, A = _t, B = _t, C = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"UID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}}), AddIDCol = Table.AddColumn( ChangedType, "ID", each if [C] <> "" then [C] else if [B] <> "" then [B] else [A], type text ) in AddIDCol