Forum Discussion

anguyen83's avatar
anguyen83
Frequent Visitor
9 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
anguyen83
Frequent Visitor
9 years ago

 

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    anguyen83
    Frequent Visitor
    9 years ago

    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's avatar
    anguyen83
    Frequent Visitor
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        VijayS
        New Member
        8 years ago

        Use 

        Table = Union(Table1, Table2)