Create a new table from columns from existing tables

[anguyen83]

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

[Anupa_Jayakody]

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]

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.

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

 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.

Thanks and Regards,

Kranthi Kumar

[BhaveshPatel]

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]

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 · 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

[anguyen83]

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]

Hi @anguyen83,

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

Regards,

Xiaoxin Sheng

[anguyen83]

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]

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

[VijayS]

Use 

Table = Union(Table1, Table2)

[anguyen83]

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]

@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