Repeating "Key" column value in Calculated Table when merging two columns from Source Table into one

[devitus]

Hi,

I am trying to create a new calculated table based on another table:

"ASINs" and "SKUs" columns from source table would be combined into a single "ASINs & SKUs" column of the calculated table (distinct values and excluding blanks from both "ASINs" and "SKUs" source columns), while repeating the respective "Key" column. Other columns (Data 1, 2, 3..) would be ignored.

I have tried the following, but the expression didn't work claiming "A table of multiple values was supplied where a single value was expected."

Calculated Table = SELECTCOLUMNS(Source Table, "Key", Source Table[Key], "ASINs & SKUs", UNION(VALUES(Source Table[ASIN]), VALUES(Source Table[SKU])))

Thanks in advance for your help!

[PaulOlding · Accepted solution]

Hi @devitus 

The UNION function needs you to supply the whole table, not just a particular column

Try this:

VAR _Unioned = 
UNION(
    SELECTCOLUMNS(
        FILTER('Table', NOT ISBLANK('Table'[ASINs])), 
        "Key", 'Table'[Key], "ASINS & SKUs", 'Table'[ASINs]
        ),
    SELECTCOLUMNS(
        FILTER('Table', NOT ISBLANK('Table'[SKUs])), 
        "Key", 'Table'[Key], "ASINS & SKUs", 'Table'[SKUs])
)
VAR _Distinct = 
SUMMARIZE(
    _Unioned,
    [Key], [ASINS & SKUs]
)

[devitus]

Absolutely amazing, thanks so much @PaulOlding! Worked perfectly 🙂