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

[SBC]

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:

1. I intend to merge the data from the CoreCurveCD, FACTORNAME, and RISK columns into a new column in the table4 page.
2. I aim to present the respective measures data in the table4 page by generating new columns or MEASURES.
3. 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

[ronrsnfld]

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

[watkinnc]

Seems like you can just use the append tables function, Table.Append(tbl1, tbl2, tbl3)

--Nate

[SBC]

Hi @watkinnc ,

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

[tharunkumarRTK · Accepted solution]

@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