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

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