Forum Discussion
Create a new table from columns from existing tables
Hi I am looking to do the following but I don't know how to go about doing it. I know how to do it via MS access but it's difficult to see how it'll work in Power BI
This is what I would like to do, with these 4 tables. Table 1 i would create a new table with two fields Timestamp & Stage. Table 2 I would append to the new table.
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
Result
I would like to create a new table. Taking those columns from the 4 tables merging them into one column and putting some sort of text field it came from those fields "TS_1, TS_2, TS_3 & TS_4"
NEW TABLE
TimeStamp Field 2
24/09/2016 TS_1
24/09/2016 TS_2
24/09/2016 TS_3
24/09/2016 TS_4
Hi anguyen83,
You can simply use below formula merge these tables.
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]))
Regards,
Xiaoxin Sheng
12 Replies
- BhaveshPatel
Super User
Assuming that you have 4 different tables with the structure outlined in below screenshot.
Table Structure
You should use Append Queries Option in the Query Editor: See the attached screenshot.
Append 3 or more tables
and once this is done, Split the column by ":" delimiter. See the screenshot.
Split column by "colon" delimiter
This will give the expected results outlined below.
Results
Hi Bhavesh
Thank you for your response.
Really do appreciate you reaching out to me because I'm struggling to with this software as I been using MS ACCESS all my life
I have below is an example. There are 4 tables and each table has one column with dates and the field name TS1-4
What I need to do is create a new table, and take the field names of each of the table and create it's own column corresponding to the contents in each table.
So at the end I will get a what is in New table
In MS access I would do a make table query taking the data from TS1 and then an append query using the remaing tables
How does it work for Power BI?
Hi anguyen83,
You can simply use below formula merge these tables.
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]))
Regards,
Xiaoxin Sheng
Hi Team,
Currently I have used queries to get average values of each PR stages and used them in clustered column chart as shown below.
I have a requirement from our stakeholder that bars in column chart should be visible separately similarly like visual below.
I think it is possible only if we can create a table like this within Power BI, please help me understand to create a table like this.
Thanks and Regards,
Kranthi Kumar
Anupa_JayakodyHelper I
Hi, How can we combine two columns from different tables with different number of columns and create a new table with a merged column??
