Forum Discussion

SBC's avatar
SBC
Helper III
2 years ago
Solved

Data Integration Challenge: Integrating Data from Table1, Table2, Table3 into Table4 Page& Reshaping

Hi, I have data from table1, table2, table3 each with different data sources. Within this dataset, measures such as (0-1), (02-05), (06-11), (12-24), and 24+ are derived through distinct calculatio...
Data Protection
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

    SBC ,

     

    Assuming 

    1. CoreCurveCD, FACTORNAME, and RISK are columns in three tables Table1, Table2 and Table3

    2. (0-1), (02-05), (06-11), (12-24), and 24+ different measures in each table.

     

    To combine all three, you can follow this dax pattern 

    union(


addcolumns(
selectcolumns (Table1, “RISKJOINT”, Table1[CoreCurveCD]  ) , 
“(0-1)”,   [(0-1)],
“(02-05)”, [(02-05)],
“(06-11)”, [(06-11)],
“(12-24)”, [(12-24)],
“24+”, [24+]
),

addcolumns(
selectcolumns (Table2, “RISKJOINT”, Table2[FACTORNAME]  ) , 
“(0-1)”,   [(0-1)],
“(02-05)”, [(02-05)],
“(06-11)”, [(06-11)],
“(12-24)”, [(12-24)],
“24+”, [24+]
),


addcolumns(
selectcolumns (Table3, “RISKJOINT”, Table3[RISK]  ) , 
“(0-1)”,   [(0-1)],
“(02-05)”, [(02-05)],
“(06-11)”, [(06-11)],
“(12-24)”, [(12-24)],
“24+”, [24+]
),

)

     I believe the logic of 4 measures for each table are different, in that case please make changes in the above code.

     

    PS: calculated tables could degrade your sematic model performance.

     


    If the post helps please give a thumbs up


    If it solves your issue, please accept it as the solution to help the other members find it more quickly.


    Tharun

     

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Here is code that takes your three tables as the data and combines them into a table that appears as your desired output:

 

let

//Change all first column names to be the same
// Trim was necessary on your examples when I copy/pasted, may not be necessary with your real data
    tbl1 = Table.RenameColumns(Table1,  
        {{Table.ColumnNames(Table1){0},"RISKJOINT"}}
        & List.Transform(List.RemoveFirstN(Table.ColumnNames(Table1),1), each {_, Text.Trim(_)})),
    tbl2 = Table.RenameColumns(Table2,  
        {{Table.ColumnNames(Table2){0},"RISKJOINT"}}
        & List.Transform(List.RemoveFirstN(Table.ColumnNames(Table2),1), each {_, Text.Trim(_)})),
    tbl3 = Table.RenameColumns(Table3,  
        {{Table.ColumnNames(Table3){0},"RISKJOINT"}}
        & List.Transform(List.RemoveFirstN(Table.ColumnNames(Table3),1), each {_, Text.Trim(_)})),
    
//combined in "reverse" order to match your output request
    allTables = Table.Combine({tbl3,tbl2,tbl1})
in
    allTables

 

Result from your data