cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
talhaparvaiz
Helper I
Helper I

how to use union SQL in powerBI

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

 

 

 

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @talhaparvaiz,

 

Create a Dimtable as:

v-xulin-mstf_0-1613369376149.png

 

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:

v-xulin-mstf_2-1613369527079.png

Here is the demo, please try it.

 

Best Regards,

Link

View solution in original post

3 REPLIES 3
v-xulin-mstf
Community Support
Community Support

Hi @talhaparvaiz,

 

Create a Dimtable as:

v-xulin-mstf_0-1613369376149.png

 

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:

v-xulin-mstf_2-1613369527079.png

Here is the demo, please try it.

 

Best Regards,

Link

View solution in original post

amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!