using union in powerBI

[talhaparvaiz]

Hi Experts,

I am downloading data from SurveyMonkey through the plugin, and the objective is to create PowerBI reports and dashboards using the data.

My table is in the following format

UserName     Application1     Application 2     Application3

ABC               AppA                AppB                 AppC

DEF               AppF                 AppE                  null

GHI               AppA                AppB                  null

JKL                AppE                 null                    null

Reports' requirement is to identify users and count of applications, something like this

ABC   3

DEF   2

GHI  2

JKL 1

Is there a way we can convert the original table to show something like this (and create reports sourcing it)

ABC               AppA               

ABC               AppB                 

ABC               AppC

DEF               AppF 

DEF               AppE 

GHI               AppA 

In a DB, I would have done it using union statements, as an example

select UserName,  Application1   as APP

union

select UserName,  Application2   as APP

union

select UserName,  Application3   as APP

any suggestions on how to do it in power BI

Thanks in advance

...

[talhaparvaiz]

this is exactly what I was looking for... Can you please walk me through the steps (or direct me to the tutorial) so that I can understand the process.

Thanks again

[PhilipTreacy · Accepted solution]

Hi @talhaparvaiz 

Yep no problem.

1. Start by loading your data into Power BI/Power Query and open the query editor (click Transform Data on the Ribbon in PBI).  Your source data looks like this

2. Select the 3 columns and then right click on one of the column headers.  Choose Unpivot Columns from the menu

3. You end up with this

4. Filter null values out of the Value column

5. Delete the Attribute column

Regards

Phil

[PhilipTreacy]

Hi @talhaparvaiz 

Download this sample PBIX file with code

You can unpivot the data in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRciwocIRQThDKWSlWJ1rJxdUNwoVSrkAqrzQnByzp7uGJoRMu6eXtg6YFJhkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserName = _t, App1 = _t, App2 = _t, App3 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"UserName"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "null")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
in
    #"Removed Columns"

Regards

Phil