Forum Discussion

AngelaB's avatar
AngelaB
Helper I
2 years ago
Solved

Combining data from two tables when data is in different format

Hello fabulous Fabric community. I will do my best to describe my problem to see if what I'm trying to do is even possible. I have also included some sample data below to show what I mean.   I have...
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI AngelaB,

    Here is the calculated table formula to union two table and convert their records to same format, you can try to use it if suitable for your requirement.

     

    Merged =
UNION (
    SELECTCOLUMNS (
        'Table1',
        'Table1'[Ward],
        'Table1'[Timepoint],
        "COG", 'Table1'[%COG],
        "MOB", 'Table1'[%MOB],
        "NUTR", 'Table1'[%NUTR]
    ),
    SUMMARIZE (
        'Table2',
        'Table2'[Ward],
        'Table2'[Timepoint],
        "COG",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[COG] = "Yes" ),
                COUNTROWS ( 'Table2' )
            ),
        "MOB",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[MOB] = "Yes" ),
                COUNTROWS ( 'Table2' )
            ),
        "NUTR",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[COG] = "Yes" ),
                COUNTROWS ( 'Table2' )
            )
    )
)

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI AngelaB,

Here is the calculated table formula to union two table and convert their records to same format, you can try to use it if suitable for your requirement.

 

Merged =
UNION (
    SELECTCOLUMNS (
        'Table1',
        'Table1'[Ward],
        'Table1'[Timepoint],
        "COG", 'Table1'[%COG],
        "MOB", 'Table1'[%MOB],
        "NUTR", 'Table1'[%NUTR]
    ),
    SUMMARIZE (
        'Table2',
        'Table2'[Ward],
        'Table2'[Timepoint],
        "COG",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[COG] = "Yes" ),
                COUNTROWS ( 'Table2' )
            ),
        "MOB",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[MOB] = "Yes" ),
                COUNTROWS ( 'Table2' )
            ),
        "NUTR",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[COG] = "Yes" ),
                COUNTROWS ( 'Table2' )
            )
    )
)

 

Regards,

Xiaoxin Sheng