Forum Discussion
Merging two asymmetrical tables
I am sure I am being thick here, and I realise that my data is probably not in the best shape to acheive this but....
I am trying to combine two tables into a single data table within PBI. Each table has two columns which could be used to link them together (max_date and project_key) - and I am trying to combine them into a summary table.
Table A - this holds data about work tickets. It has a row for each update to the work ticket, and the data is like:
| issue_id | max_date | project_key | from | from_date | to | to_date |
| 1 | 27/08/2024 | os | to do | 02/08/2024 | in progress | 05/08/2024 |
| 2 | 27/08/2024 | os | to do | 03/08/2024 | in progress | 06/08/2024 |
| 4 | 27/08/2024 | cus | to do | 03/08/2024 | in progress | 04/08/2024 |
Table B - this holds 1 row for each issue in a work package - but not all work tickets are in a work package. The columns are:
| issue_id | max_date | project_key | sprint_id |
| 1 | 27/08/2024 | os | 1234 |
| 2 | 27/08/2024 | os | 1234 |
| 3 | 27/08/2024 | os | 4321 |
I am trying to create a summary table using both tables, such that there will be at least 1 row for each max_date and project_key combination. So far so good - now comes the bit that is tripping me up.
I'm trying to get a table like below - with a row for each max_date/project_key combination. Where there is more than 1 matching row in table B, that should add further rows to the table, so that we get something like the below table. Likewise, if there is no matching data in table B, it would just leave the sprint_id field blank.
| max_date | project_key | sprint_id |
| 27/08/2024 | os | 1234 |
| 27/08/2024 | os | 4321 |
| 27/08/2024 | cus |
I've been playing with the different join options in DAX but can't work out how to do the above. I'm sure its a super simple job, but my brain is just not seeing the solution.
Okay, so after much bang of my head against a DAX shaped brick wall, and using the helpful suggestions by lbendlin I have managed to get closer to what I was looking for. In the end I used the followign approach:
I defined two tables using SUMMARIZE and SELECTCOLUMNS, then using NATURALLEFTOUTERJOIN to join them together. One thing to note was, data lineage meant that my join kept failing. I got around this by appending a 0 to the key value being used to join tables - this broke the lineage and allowed the join to work. I've included the code below in case it helps people to understand the approach.
var tableA = SELECTCOLUMNS(SUMMARIZE(sprint, sprint[squad_sprint], sprint[sprint_id], sprint[name]), "squad_sprint", [squad_sprint]&"0", "sprint_id", [sprint_id], [name]) var tableB = SELECTCOLUMNS(SUMMARIZE(history_sprint_summary, history_sprint_summary[squad_sprint], history_sprint_summary[project_key], history_sprint_summary[max_sprint_date]), "squad_sprint", [squad_sprint]&"0", "project_key", [project_key], "max_sprint_date",[max_sprint_date]) var result = NATURALLEFTOUTERJOIN(tableB, tableA) RETURN result
4 Replies
Do the merge in Power Query, there you can specify multiple join columns.
let Source = Table.NestedJoin(#"Table A", {"max_date", "project_key"}, #"Table B", {"max_date", "project_key"}, "Table B", JoinKind.LeftOuter), #"Expanded Table B" = Table.ExpandTableColumn(Source, "Table B", {"sprint_id"}, {"sprint_id"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table B",{"max_date", "project_key", "sprint_id"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns") in #"Removed Duplicates"Ah amazing thank you, I will try that and let you know how I get on. Will that method work if both input tables are Dax generated, not imported?
No, in that scenario you need to create a composite key [max date]+[project key] in both tables and then join via that composite key.
Okay, so after much bang of my head against a DAX shaped brick wall, and using the helpful suggestions by lbendlin I have managed to get closer to what I was looking for. In the end I used the followign approach:
I defined two tables using SUMMARIZE and SELECTCOLUMNS, then using NATURALLEFTOUTERJOIN to join them together. One thing to note was, data lineage meant that my join kept failing. I got around this by appending a 0 to the key value being used to join tables - this broke the lineage and allowed the join to work. I've included the code below in case it helps people to understand the approach.
var tableA = SELECTCOLUMNS(SUMMARIZE(sprint, sprint[squad_sprint], sprint[sprint_id], sprint[name]), "squad_sprint", [squad_sprint]&"0", "sprint_id", [sprint_id], [name]) var tableB = SELECTCOLUMNS(SUMMARIZE(history_sprint_summary, history_sprint_summary[squad_sprint], history_sprint_summary[project_key], history_sprint_summary[max_sprint_date]), "squad_sprint", [squad_sprint]&"0", "project_key", [project_key], "max_sprint_date",[max_sprint_date]) var result = NATURALLEFTOUTERJOIN(tableB, tableA) RETURN result
