Forum Discussion

devitus's avatar
devitus
Frequent Visitor
4 years ago
Solved

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

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's avatar
    PaulOlding
    4 years ago

    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]
)

2 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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]
)