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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

 

 

 

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.

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.

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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