Forum Discussion
Relationship Cardinality
I can't get One to Many to work for a new relationship. I read through a ton of posts and did the following and still can't get it to work.
In PowerQuery:
#"Removed Blank Rows" = Table.SelectRows(#"Sorted Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Blank Rows", {"Serial Number"}),
#"Uppercased Text" = Table.TransformColumns(#"Removed Errors",{{"Serial Number", Text.Upper, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Serial Number", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Serial Number", Text.Clean, type text}}),
#"Removed Duplicates" = Table.Distinct(#"Cleaned Text", {"Serial Number"}),
#"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Serial Number", Order.Descending}}),
#"Removed Duplicates1" = Table.Distinct(#"Sorted Rows1", {"Serial Number"})
I then created the following measures to make sure that Serial Number was in fact truly clean:
It is not clear how your tables look like, but you can add a list out of the Key Column and then convert it to table with non duplicate values and finaly use it as a bridge table
2 Replies
It is not clear how your tables look like, but you can add a list out of the Key Column and then convert it to table with non duplicate values and finaly use it as a bridge table
I ended up removing all of the other columns from the table, and then the relationship to other tables was autodetected as one to many. Just like you said a single bridge table with just one column fixed the issue.
