Forum Discussion

donodackal's avatar
donodackal
Helper I
4 years ago
Solved

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

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. 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

3 Replies

  • samdthompson's avatar
    samdthompson
    Memorable Member
    4 years ago

    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's avatar
      donodackal
      Helper I
      4 years ago

      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's avatar
        v-easonf-msft
        Community Support
        4 years ago

        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