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 - th...
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

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