Forum Discussion
Using SelectColumns to Combine Several Excel Sheets
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
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.
4 Replies
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.
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?SELECTCOLUMN is a table function and should be used in a calculated table. What are trying this function for as a measure?
