Question

How can I retrieve distinct values from multiple columns using Power BI?

To retrieve distinct values from multiple columns in Power BI, use the "UNION" and "SELECTCOLUMNS" DAX functions to combine columns into a single virtual table and then apply the "DISTINCT" function for unique values.

Answer 1

In Power BI, obtaining unique values from certain columns can be realized by employing a variety of techniques depending on the target and level of complexity of the data. Here’s an easy instance relying on Power Query and DAX:

• Method 1: - Power Query

Open Power Query Editor:

For Power BI Desktop, the option is on the “Home” tab. Click on “Transform Data” to trigger Power Query Editor.

Select Your Table:

In the left pane of Power Query, click the table containing the distinct values you wish to extract.

Remove Other Columns:

If only the distinct names of the columns are of concern, go ahead and select those columns. Select the columns, right-click on them, and select “Remove Other Columns.”

Remove Duplicates:

With the appropriate columns highlighted, Duplicates can be removed in Power Query Editor under the ‘Home’ tab. Click on ‘Remove Rows’ and then choose ‘Remove Duplicates.’ This will return a table with distinct values only based on the column selection mentioned.

Load Data:

Lastly, select “Close & Apply” to bring the modified data back into Power BI.

Method 2: Using DAX Since there is a need to produce a table from numerous columns and this table requires different values for some of its columns, DAX can be handy in this task as explained in the following steps:

Create a New Table:

In Power BI Desktop, navigate to the “Modeling” tab and then select “New Table.”

Write the DAX Formula:

The following DAX formula can be used to create a table with distinct values for the given columns.

DistinctValuesTable = DISTINCT(UNION(VALUES(Table[Column1]), VALUES(Table[Column2]),VALUES(Table[Column3]))) 

Substitute Table with the name of your actual table and the names Column1, Column2, and Column3 with those of columns from which you require the distinct values. This formula merges the distinct values only from the mentioned columns to form a new table. 
Look at the New Table:

Once the table is created, you can tap on it within the data model and analyze it or use it for visualization.