Questions about relation

[gakka9272]

Questions about relation Please tell me how to relate the relation with multiple items. Currently, only one item can be related between tables.

[stretcharm · Accepted 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.

[gakka9272]

thank you