Forum Discussion
Anonymous
7 years agoNot applicable
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...
- 7 years ago
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
LivioLanzo
7 years agoSolution Sage
can you post your sample table ?
Anonymous
7 years agoNot applicable
| 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 |
- LivioLanzo7 years agoSolution Sage
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- Anonymous7 years agoNot applicable
Thank you for your help and reply. I am trying to understand where in your code that it checkes the value in the TableB column to one of those sought from the table A [Key] column.
- LivioLanzo7 years agoSolution Sage
Hi spider, the code consolidates into column your IDs in order to create a collumn that you can relate to the other table in your data model. is that now what you were after?