How to create a single table using data from different data sources?

[donodackal]

Hi there

Would appreciate any help. I have 10 different data sources that are in different formats (not consistent with each other) and the only common data in each data source is one column - the dollar value.

How can I create a single table that details the sum dollar value from each of the data sources? I was thinking of creating/rearranging columns in each of the data sources so I can merge them all. 

[samdthompson]

Hello, might need a bit more detail to give you a good answer. Should the tables all be combined? If so then its probably a power query solution by appending the tables. Are the tables quite different groups of Facts? if so then its probaly better to write individual sum measures and combine. Do the tables have relationships to dimensional tables?

[donodackal]

Hi Sam

They are 10 fact tables that are not the same structure. Had it been, I think my next step was to append the tables to form a single source for my matrix visualisation. Because the data across each of the tables are not consistent I can only use the product name, $ cost and data of expense as it is the only common data between the tables. 

[v-easonf-msft · Accepted solution]

Hi, @donodackal 

You can try to create a calculated table to combine these table columns.

Table =
VAR tab1 =
    SELECTCOLUMNS (
        Table1,
        "ProductName", Table1[product name],
        "Cost", Table1[$ cost],
        "Expense", Table1[expense],
        "datasource", "table1"
    )
VAR tab2 =
    SELECTCOLUMNS (
        Table2,
        "ProductName", Table2[product name],
        "Cost", Table2[$ cost],
        "Expense", Table2[expense],
        "datasource", "table2"
    )
VAR tab3 =
    SELECTCOLUMNS (
        Table3,
        "ProductName", Table3[product name],
        "Cost", Table3[$ cost],
        "Expense", Table3[expense],
        "datasource", "table2"
    )
RETURN
    UNION ( tab1, tab2, tab3 )

Please check my sample file for more information.

If it doesn't work, please share more details.

Best Regards,
Community Support Team _ Eason