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 !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

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!