Unpivot column using DAX shows error "Column name already exist"

[949184_soy]

Hi everyone I am trying to unpivot this columns using DAX, the name is TABLE

Into this:

Using this code: 

TablaAuxiliar = 
UNION(
    GENERATE(
        FILTER(TABLA, NOT(ISBLANK([NAME_01]))),
        ROW(
            "Id", [Id],
            “Name", [NAME_01],
            “Company", [COMPANY_01],
            "Department", [DEPARTMENT_01],
            “Role", [ROLE_01]
        )
    ),
    GENERATE(
        FILTER(TABLA, NOT(ISBLANK([NAME_02]))),
        ROW(
            "Id", [Id],
            "Name", [NAME_02],
            "Company", [COMPANY_02],
            "Department", [DEPARTMENT_02],
            " Role", [ROLE_02]
        )
    )     
    )

But I am geeting this error 

"The Column with the name of Id already exist in the TablaAuxiliar Table"

Any help will be appreciated. 

Regards

[audreygerred · Accepted solution]

You can create what you need in Power Query. I would remove the _02 columns as a step in Power Query, then rename the _01 columns to just be NAME, COMPANY, DEPARTMENT, and ROLE. Next, duplicate that query and remove your rename and removed column steps, then remove the _01 columns and rename the _02 columns to NAME, COMPANY, DEPARTMENT, and ROLE. Next, append the two queries together wither as a new query or within one of the two that are there.

[audreygerred · Accepted solution]

You can create what you need in Power Query. I would remove the _02 columns as a step in Power Query, then rename the _01 columns to just be NAME, COMPANY, DEPARTMENT, and ROLE. Next, duplicate that query and remove your rename and removed column steps, then remove the _01 columns and rename the _02 columns to NAME, COMPANY, DEPARTMENT, and ROLE. Next, append the two queries together wither as a new query or within one of the two that are there.

[949184_soy]

Thank you for your response, I manage to append the tables 01 and 02. Despide of the above I wonder if there is a DAX formula because in the real dataset has 20 columns 01 to 20. 

Cheers

[audreygerred · Accepted solution]

Transformations like this should be pushed as far upstream as possible. If it is not possible to have the source be the way you need it, the next best place is Power Query. Doing it via DAX (if even possible) would not be the best approach. Hope this helps!