Using SelectColumns to Combine Several Excel Sheets

[Tabular]

Hello,

I have several Excel sheets with 'product' info and the date they were 'sold'. The columns for the products is split across varying columns and do not line up. I was able to circumvent this using the code below:

Slicer = 

        UNION(
        SELECTCOLUMNS(
            'Products1',
            "Scope", 'Products1'[Column2] 
            ),
        SELECTCOLUMNS(
            'Products1',
            "Scope", 'Products1'[Column3]
            ),

        SELECTCOLUMNS(
            'Products2',
            "Scope", 'Products2'[Column5]
            ),

....

     )

(... = Repeated for many columns)

This code perfectly, but I am running into problems trying to take the date 'sold' associated with each product in each column. Using this (https://dax.guide/selectcolumns/) information, I thought I could just write the following addition for each excel workbook to create a second column and create a two column, many row table, but I get a syntax issue with SelectColumns

Slicer = 

        UNION(
        SELECTCOLUMNS(
            'Products1',
            "Scope", 'Products1'[Column2] 
            ),
        SELECTCOLUMNS(
            'Products1',
            "Date", 'Products1'[Column8]
            )

     ....

If you can think of a better way than this please let me know.
Thanks

[danextian · Accepted solution]

Hi @Tabular ,

This is how you select multiple columns from the same table using SELECTCOLUMNS

=
SELECTCOLUMNS (
    'Products1',
    "Scope", 'Products1'[Column2],
    "Date", 'Products1'[Column8]
)

Also, instead of creating a untion in DAX, have you tried appending each table to one another in Power Query? That basically creates a union as well.

[Tabular]

Thank you. When I try this I receive the error "This expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
I've tried to use append but since the columns I want combined are not in the same column across each sheet, I'm unsure of how to allign them. What would this function be called?

[danextian]

SELECTCOLUMN is a table function and should be used in a calculated table. What are trying this function for as a measure?

[Tabular]

Yes that was my problem. I didn't realize I set the other up this way, thank you.