Power BI: extract columns from a table to another one

When I have multiple tables and I want to insert a specific column from a table to an existing one or to a new one, I will use one of those 2 functions:

  • RELATED, for instance RELATED('table'[argument]). To be used to add a new column to an existing table. For this formula to work, tables should have a relationship
  • SELECTCOLUMNS, for instance SELECTCOLUMNS('table',[argument]). To be used to create a new table (relationship no needed) with an existing column

NOTE: change “table” and “argument” by yours.

This formula will put the same name as “argument” but if I want to put a different name, I will use this one:

SELECTCOLUMNS('table',"different name",[argument])

NOTE: change “different name” by yours.

If I want to add multiple columns, just add another [argument] like that:

  • SELECTCOLUMNS('table',[argument1],[argument2])
  • SELECTCOLUMNS('table',"different name1",[argument1],"different name2",[argument2])

NOTE: instead to use [argument1], I can use a specific criteria, for instance:

SELECTCOLUMNS('table',"different name1",”specific criteria”,"different name2",[argument2])

The CALCULATETABLE function can be used as a filter, for instance, if I have a huge data and I want to extract only data for a specific group:

SELECTCOLUMNS(CALCULATETABLE('table1',FILTER('table2','table2'[argument]="value")),"different name1",[argument1],"different name2",[argument2])

NOTE: argument1 and argument2 are from table1

To get only unique values, I will add the DISTINCT function:

DISTINCT(SELECTCOLUMNS('table',"different name1",[argument1],"different name2",[argument2]))

To exclude blank values or others, I will add the FILTER function, for instance:

FILTER(DISTINCT(SELECTCOLUMNS('table',"different name1",[argument1],"different name2",[argument2])),NOT(ISBLANK([different name1])))

NOTE: “different name1” is the column where there are blank cells

Now if I add the UNION function, I will be able to merge columns from 2 different tables into 1 same column:

UNION(SELECTCOLUMNS('table1',[argument]),SELECTCOLUMNS('table2',[argument]))

And to not repeat again, if I want to add more columns or put a particular name, just follow the same formula as above.

Interesting Management