How to create a new table from many other similar tables

Power Query
1

Hey guys,

I have four tables and want to do the following:
Table 1 - create a new table with two fields, Timestamp and Stage
Table 2 - append to the new table

Here is a sample of what the tables would contain:
Table 1
TS_1:24/09/2016

Table 2
TS_2: 24/09/2016

Table 3
TS_3: 24/09/2016

Table 4
TS_4: 24/09/2016

In the end, I want to merge the columns from the four tables into a new table. And then I would put some sort of text field indicating that it came from those respective fields.

The new table would look like this:
TimeStamp Field 2
24/09/2016 TS_1
24/09/2016 TS_2
24/09/2016 TS_3
24/09/2016 TS_4

I don’t know how to do this in Power BI. Can anyone help me out? Thanks.

2

The UNION function is the key to answering this one.

To merge these tables, you can simply use the formula below:

New Table= UNION(SELECTCOLUMNS(Table1,"Stage","TS1","Date",[TS1]),SELECTCOLUMNS(Table2,"Stage","TS2","Date",[TS2]),SELECTCOLUMNS(Table3,"Stage","TS3","Date",[TS3]),SELECTCOLUMNS(Table4,"Stage","TS4","Date",[TS4]))

You could also use a combination of UNION and SUMMARIZE also.

Check out the below to learn a bit more about UNION.

UNION is a table function in DAX. It’s an important concept to learn and master, around how to use these functions.

Check out the below to learn a bit more about these.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000575

This would be a great course to review here as well.

Something to do pretty early on as you look to master a few key fundamentals in Power BI.

All the best

Sam

3

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.