cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anguyen83
Frequent Visitor

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

1 ACCEPTED SOLUTION

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

10 REPLIES 10
BhaveshPatel
Community Champion
Community Champion

 

Assuming that you have 4 different tables with the structure outlined in below screenshot.

Table StructureTable Structure

You should use Append Queries Option in the Query Editor: See the attached screenshot.

Append 3 or more tablesAppend 3 or more tables

and once this is done, Split the column by ":" delimiter. See the screenshot.

Split column by "colon" delimiterSplit column by "colon" delimiter

 

This will give the expected results outlined below.

ResultsResults

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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?

 

 

 

Capture.JPG

 

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Capture.JPG

 

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

Hi @anguyen83,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi

 

I fixed it thank you

 

LS_Consolidated = Union(SELECTCOLUMNS('Lead',"Stage","TS1","Timestamp",[LS1_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Lead',"Stage","TS2","Timestamp",[LS2_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS3","Timestamp",[LS3_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS4","Timestamp",[LS4_Timestamp__c].[Date],"Account_Id",[Id]))

 

So now the records I brought in there is Null under the timestamp field. I was wondering in this code is their a way to filter <> Null before the table is create or before you append the data?

Hi @anguyen83,

 

>>So now the records I brought in there is Null under the timestamp field. I was wondering in this code is their a way to filter <> Null before the table is create or before you append the data?

 

You can use filter function to filter the blank value, for example:

 

LS_Consolidated =
Filter(
Union(SELECTCOLUMNS('Lead',"Stage","TS1","Timestamp",[LS1_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Lead',"Stage","TS2","Timestamp",[LS2_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS3","Timestamp",[LS3_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS4","Timestamp",[LS4_Timestamp__c].[Date],"Account_Id",[Id])),[Timestamp]<>blank())

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Use 

Table = Union(Table1, Table2)

Hi Xiaoxin

 

Not sure what you mean by that?

 

Would you be able to provide an example? sorry the language of DAX is new to me.

 

Vvelarde
Community Champion
Community Champion

@anguyen83

 

Follow this steps:

 

1. Go to Edit Query

 

2. Select Append Queries as New

 

Ap1.png

 

3.  Select all your tables to Append

 

Ap2.png

 

4. You have these results

 

Ap3.png

 

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

 

Ap4.png

 

6. You have this:

 

Ap5.png

 

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

 

8. Close & Apply

 

9. Ready

 




Lima - Peru

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors