Forum Discussion
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 calculations for the table1, table2, and table3 pages. These measures are not directly accessible within the data source or via power query.
The column names are CoreCurveCD, FACTORNAME, and RISK respectively.
Table1:
CoreCurveCD | (0-1) | (02-05) | (06-11) | (12-24) | 24+ |
benchmarkA | 78 | 76 | 89 | 7 | 92 |
Bentlie | 98 | 7 | 583 | 42 | 54 |
Denmark | 9 | 94 | 78 | 987 | 908 |
Table2:
FACTORNAME | (0-1) | (02-05) | (06-11) | (12-24) | 24+ |
BRENK | 72 | 32 | 23 | 34 | 54 |
Table3:
RISK | (0-1) | (02-05) | (06-11) | (12-24) | 24+ |
BRENCE_FEATURES | 145 | 546 | 453 | 412 | 213 |
LAUCE | 134 | 765 | 234 | 421 | 243 |
BRINT | 132 | 567 | 432 | 321 | 256 |
Required to achieve the desired result in the new page named as table4 page:
- I intend to merge the data from the CoreCurveCD, FACTORNAME, and RISK columns into a new column in the table4 page.
- I aim to present the respective measures data in the table4 page by generating new columns or MEASURES.
- The desired outcome in the table4 page would look like this:
RISKJOINT | (0-1) | (02-05) | (06-11) | (12-24) | 24+ |
BRENCE_FEATURES | 145 | 546 | 453 | 412 | 213 |
LAUCE | 134 | 765 | 234 | 421 | 243 |
BRINT | 132 | 567 | 432 | 321 | 256 |
BRENK | 72 | 32 | 23 | 34 | 54 |
benchmarkA | 78 | 76 | 89 | 7 | 92 |
Bentlie | 98 | 7 | 583 | 42 | 54 |
Denmark | 9 | 94 | 78 | 987 | 908 |
Thanks,
SBC
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
4 Replies
Hi Anonymous ,
Thank you for your suggestion on using the append tables function. I appreciate your input and the simplicity of the approach you've described. I did try appending the tables in Power Query earlier, which worked well for consolidating the CoreCurveCD, FACTORNAME, and RISK columns into the new RISKJOINT column. However, I encountered a challenge when attempting to include the additional measure-based columns (0-1, 02-05, 06-11, 12-24, 24+). Each table uses a unique logic for bucketing these measures, and unfortunately, these are not directly pulled through the append function. I need to ensure that the data for these measure columns is displayed correctly in the combined table4 page, reflecting the distinct calculations from each original table. If you have any further advice on how to achieve this, I would be very grateful for your expertise.
Thanks,
SBC
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
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 allTablesResult from your data
