Forum Discussion

anguyen83's avatar
anguyen83
Frequent Visitor
9 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

anguyen83's avatar
anguyen83
Frequent Visitor
9 years ago

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?

 

 

 

 

 

 

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

anguyen83

 

Follow this steps:

 

1. Go to Edit Query

 

2. Select Append Queries as New

 

 

3.  Select all your tables to Append

 

 

4. You have these results

 

 

5. Almost Close. Now Select your 4 columns TS1...TS4. and Unpivot Columns

 

 

6. You have this:

 

 

7. Finally: Change the name of both Columns : Stage , Date

 

8. Close & Apply

 

9. Ready