Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
Anonymous
Not applicable

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

View solution in original post

12 REPLIES 12

Hi, How can we combine two columns from different tables with different number of columns and create a new table with a merged column??

kranthi77
New Member

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.

PBI Table.PNG

 

PBI Bar Chart Visual.png

I have a requirement from our stakeholder that bars in column chart should be visible separately similarly like visual below.

 

Column Chart.png

 

 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.

kranthi77_0-1651308795819.png

Thanks and Regards,

Kranthi Kumar

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

 

 

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

Hi @anguyen83,

 

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

 

Regards,

Xiaoxin Sheng

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?

Anonymous
Not applicable

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

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.

 

@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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.