Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Combine Colums

Hey @ all.

My current problem is this:

I have 4 different Excel tables, each of which I have created a calculated column from integers. (Valid_CSIS , Valid_Gen 8....)

In the end I want to have all 4 integer columns from these calculated colums in a single table with only one column. ("Valid"

I cannot use the Union function because the 4 source files all have different numbers of columns.

 

Thank you for your help.

Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    You can create common dimension tables across all 4 tables and the create a combined measure and use that in visuals along with common dimensions

     

    Measure = sum(Table1[Valid_CSIS])+ sum(Table2[Valid_CSIS])+ sum(Table3[Valid_CSIS])+ sum(Table4[Valid_CSIS])

     

    Another way to union is inside union use selectcolumns or summarize

     

    examples

    Summary = UNION(
    SUMMARIZE('Table1','Table1'[Customer],'Table1'[created_date],'Table1'[DA Status],"Sales",sum('Table1'[Sales]),"Invoices",count('Table1'[ID]),"Time",DIVIDE(SUM('Table1'[Ship Time]),1),"Stage"," Ship","Gross",sum('Table1'[inv_amount]),"Net",Sum('Table1'[Net Calc Amount]))
    ,SUMMARIZE('Table2','Table2'[Customer],'Table1'[created_date],'Table2'[AA Status],"Sales",sum('Table2'[Sales]),"Invoices",count('Table2'[ID]),"Time",DIVIDE(SUM('Table1'[Order Time]),1),"Stage","Order","Gross",sum('Table1'[inv_amount]),"Net",Sum('Table1'[Net Calc Amount]))
    )

     

     

    union(
    selectcolumns(table1,"ColA", table1[Column A],"ColA", table1[Column B],"ColF", table1[Column F]),
    selectcolumns(table2,"ColA", table2[Column A],"ColA", table2[Column B],"ColF", table2[Column F])
    )

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Use UNION except use SELECTCOLUMNS inside your UNION so:

     

    Table = 

      UNION(

        SELECTCOLUMNS('Table1',"Value",[Value blah blah blah]),

        SELECTCOLUMNS('Table2',"Value",[Value blah blah blah]),

        SELECTCOLUMNS('Table3',"Value",[Value blah blah blah]),

        SELECTCOLUMNS('Table4',"Value",[Value blah blah blah])

      )