how to use union SQL in powerBI

[talhaparvaiz]

Hi,

I have a table that I am downloading power BI that looks something like this

User  App1   App1_description   App1_group  App2   App2_description   App2_group

abc    a1       a1 some desc         A1_groupA    A2        a2 some desc         A2_groupA

def     a11    a11 some desc       A1_groupA    A12        a12 some desc     A2_groupB

I want the table to be converted to the following format

User  App   App_description   App_group  

abc    a1       a1 some desc         A1_groupA    

def     a11    a11 some desc       A1_groupA    

abc    A2      a2 some desc         A2_groupA

def   A12     a12 some desc     A2_groupB

I am just doing a union SQL

select user, app1 as app, app1_description as app_description, app1_group as app_group

union all

select user, app2 as app, app2_description as app_description, app2_group as app_group

Can you please advise how to do union (STEP BY STEP) in power BI

Thanks

[v-xulin-mstf · Accepted solution]

Hi @talhaparvaiz,

Create a Dimtable as:

Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "User", 'Table'[User],
        "App", 'Table'[App1],
        "App_description", 'Table'[App1_description],
        "App_group", 'Table'[App1_group]
    ),
    SELECTCOLUMNS (
        'Table',
        "User", 'Table'[User],
        "App", 'Table'[App2],
        "App_description", 'Table'[App2_description],
        "App_group",'Table'[App2_group]
    )
)

Here is the output:

Here is the demo, please try it.

Best Regards,

Link

[amitchandak]

@talhaparvaiz , Unpivot more than once.

refer : https://kohera.be/blog/power-bi/how-to-unpivot-twice/

Union in Dax with select columns

 union(
selectcolumns(Table, "User",[User] , "App" ,[App1], "App_description",[App1_description] , "App_group",[App1_group])
selectcolumns(Table, "User",[User] , "App" ,[App2], "App_description",[App2_description] , "App_group",[App2_group])
)

[talhaparvaiz]

Hi, I will try out the unpivot option

for DAX, can you please advise how to run the script mentioned above... sorry, I am new to DAX and would appreciate step by step guidence.

Thanks