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

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

 

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

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?

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 Xiaoxin

       

      Thank you for your help. The first two selectcolumns worked pulling the data from the table called 'lead' however when I add in the remaing selectcolumns getting the data from a table called 'Contacts' it doesn't seem to work

       

      Is there something wrong with my query? I'm not familar with DAX 

       

      It's halfway their

       

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi anguyen83,

         

        The first parameter of selectcolumns function is table type.(based on your screenshot, I find you input a column)

         

        Regards,

        Xiaoxin Sheng

  • Vvelarde's avatar
    Vvelarde
    Community 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