cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anguyen83 Frequent Visitor
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

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Create a new table from columns from existing tables

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

10 REPLIES 10
BhaveshPatel Super Contributor
Super Contributor

Re: Create a new table from columns from existing tables

 

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

c1.PNGTable Structure

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

c2.PNGAppend 3 or more tables

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

c3.PNGSplit column by "colon" delimiter

 

This will give the expected results outlined below.

c4.PNGResults

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.
anguyen83 Frequent Visitor
Frequent Visitor

Re: Create a new table from columns from existing tables

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

 

 

 

Vvelarde Super Contributor
Super Contributor

Re: Create a new table from columns from existing tables

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Community Support Team
Community Support Team

Re: Create a new table from columns from existing tables

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

anguyen83 Frequent Visitor
Frequent Visitor

Re: Create a new table from columns from existing tables

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

Community Support Team
Community Support Team

Re: Create a new table from columns from existing tables

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
anguyen83 Frequent Visitor
Frequent Visitor

Re: Create a new table from columns from existing tables

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.

 

anguyen83 Frequent Visitor
Frequent Visitor

Re: Create a new table from columns from existing tables

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?

Community Support Team
Community Support Team

Re: Create a new table from columns from existing tables

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 744 guests
Please welcome our newest community members: