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.
Questions about relation Please tell me how to relate the relation with multiple items. Currently, only one item can be related between tables.
Solved! Go to Solution.
Create a composite key by joining fields.
I tend combine the fields with a | delimiter
e.g. Here I duplicate columns in the Query Editor then combine them. You can also do the same by creating a Custom Column, however you need to cast to text first.
#"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "Store Code", "Store Code - Copy"), #"Duplicated Column" = Table.DuplicateColumn(#"Duplicated Column1", "Date", "Date - Copy"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Store Code - Copy", type text}, {"Date - Copy", type text}}, "en-GB"),{"Store Code - Copy", "Date - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"StoreDateKey"), #"Added Custom1" = Table.AddColumn(#"Merged Columns", "StoreDateKey1", each Number.ToText([Store Code]) & "|" & Date.ToText([Date]))
You can also do it in Dax by created a computed column
StoreKey3 = Actual[Store Code] & "|" & FORMAT(Actual[Date], "YYYYMMDD")
These can then be used in Query editor merges or Relationships.
Create a composite key by joining fields.
I tend combine the fields with a | delimiter
e.g. Here I duplicate columns in the Query Editor then combine them. You can also do the same by creating a Custom Column, however you need to cast to text first.
#"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "Store Code", "Store Code - Copy"), #"Duplicated Column" = Table.DuplicateColumn(#"Duplicated Column1", "Date", "Date - Copy"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Store Code - Copy", type text}, {"Date - Copy", type text}}, "en-GB"),{"Store Code - Copy", "Date - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"StoreDateKey"), #"Added Custom1" = Table.AddColumn(#"Merged Columns", "StoreDateKey1", each Number.ToText([Store Code]) & "|" & Date.ToText([Date]))
You can also do it in Dax by created a computed column
StoreKey3 = Actual[Store Code] & "|" & FORMAT(Actual[Date], "YYYYMMDD")
These can then be used in Query editor merges or Relationships.
thank you
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.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |