Forum Discussion
Solved
Combine date from two tables and split rows over columns
I need to combine two sets of data (two tables) that have some identical columns. Each row has an ID Num which is assigned to an individual. Each individual may hold a number of qualifications. There are 4 types of qualification and up to 4 levels in each qualification. The second table contains the data relating to the individual's qualification(s), with each row representing a qualification held by the individual. I want to merge these tables so that each individual only has 1 row and there a number of columns relating to each type of qualification. There are also two additional columns for the date the qualification is acquired and expires.
Table 1 and Table 2 below set out an example of the data and Table 3 shows how I want the data to turn out.
Table 1
| ID Num | ID2 | First name | Last name | Address | |
| 1 | 444 | Paul | Smith | [email protected] | address001 |
| 2 | 555 | John | Doe | [email protected] | address002 |
| 3 | 333 | Jane | Smith | [email protected] | address003 |
| 4 | 111 | Tony | Johnson | [email protected] | address004 |
| 5 | 222 | Derek | Jones | [email protected] | address005 |
Table 2
| ID Num | First name | Last name | Address | Qualification | Qual acquire | Qual expired | |
| 1 | Paul | Smith | [email protected] | address001 | type1 Level 1 | 4/08/2015 | 31/12/2018 |
| 1 | Paul | Smith | [email protected] | address001 | type2 Level 2 | 8/06/2016 | 31/12/2019 |
| 1 | Paul | Smith | [email protected] | address001 | type3 level 3 | 13/04/2017 | 31/12/2020 |
| 2 | John | Doe | [email protected] | address002 | type4 level 1 | 16/02/2018 | 31/12/2021 |
| 2 | John | Doe | [email protected] | address002 | type 2 level 3 | 22/12/2018 | 31/12/2021 |
| 3 | Jane | Smith | [email protected] | address003 | type 1 level 4 | 27/10/2014 | 31/12/2019 |
| 3 | Jane | Smith | [email protected] | address003 | type 3 level 2 | 6/07/2017 | 31/12/2020 |
| 3 | Jane | Smith | [email protected] | address003 | type 2 level 1 | 31/08/2018 | 31/12/2021 |
| 4 | Tony | Johnson | [email protected] | address004 | type 2 level 4 | 11/05/2013 | 31/12/2018 |
| 5 | Derek | Jones | [email protected] | address005 | type 1 level 4 | 16/03/2012 | 31/12/2018 |
Table 3
| ID Num | ID2 Num | First Name | Last Name | Address | Qual type 1 | Qual type 1 acquire | Qual type 1 acquire | Qual type 2 | Qual type 2 acquire | Qual type 2 acquire | Qual type 3 | Qual type 3 acquire | Qual type 3 acquire | Qual type 4 | Qual type 4 acquire | Qual type 4 acquire | |
| 1 | 444 | Paul | Smith | [email protected] | address001 | type1 Level 1 | 4/08/2015 | 31/12/2018 | type2 Level 2 | 8/06/2016 | 31/12/2019 | type3 level 3 | 13/04/2017 | 31/12/2020 | |||
| 2 | 555 | John | Doe | [email protected] | address002 | type 2 level 3 | 22/12/2018 | 31/12/2021 | type4 level 1 | 16/02/2018 | ######## | ||||||
| 3 | 333 | Jane | Smith | [email protected] | address003 | type 1 level 4 | 27/10/2014 | 31/12/2019 | type 2 level 1 | 31/08/2018 | 31/12/2021 | type 3 level 2 | 6/07/2017 | 31/12/2020 | |||
| 4 | 111 | Tony | Johnson | [email protected] | address004 | type 2 level 4 | 11/05/2013 | 31/12/2018 | |||||||||
| 5 | 222 | Derek | Jones | [email protected] | address005 | type 1 level 4 | 16/03/2012 | 31/12/2018 |
I have been able to merge the tables but not able to then merge rows with identical ID Nums. Further, I cannot work out how I can have the query look through the qualification column and work out which column the qualification should be in the merged table.
Thank you for any assistance in advanced.
adamvainauskas If you are sure that the number of qualifications will not be more than 4, then you can achieve this by using below DAX as "New Table" (Ofcourse, you can achieve this through Power Query as well with multiple steps of pivot, unpivot and merge...) But it will be a straight forward in DAX assuming the max qualifications will not exceed 4 without having any additional transformations.
We are here adding additional fields to the existing table (Table 1 in your case) and result into a new table.
Test113Out = ADDCOLUMNS(Test113PQ1, "Qual Type 1",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 1"),"Qual",[Qualification]), "Qual Type 1 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 1"),"QualAcquire",[QualAcquired]), "Qual Type 1 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 1"),"QualExpire",[QualExpired]), "Qual Type 2",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 2"),"Qual",[Qualification]), "Qual Type 2 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 2"),"QualAcquire",[QualAcquired]), "Qual Type 2 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 2"),"QualExpire",[QualExpired]), "Qual Type 3",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 3"),"Qual",[Qualification]), "Qual Type 3 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 3"),"QualAcquire",[QualAcquired]), "Qual Type 3 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 3"),"QualExpire",[QualExpired]), "Qual Type 4",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 4"),"Qual",[Qualification]), "Qual Type 4 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 4"),"QualAcquire",[QualAcquired]), "Qual Type 4 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 4"),"QualExpire",[QualExpired]) )Hi adamvainauskas,
Here is the solution of Power Query (M). Please download the demo in the attachment. Please note: many steps can be done through the UI of Query Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZPLTsMwEEV/xcq6ws82YQdSQKKqEAh2URdWO6gWeREniP49M6kDURtYtKuZayvn2jfjLIseUvbYFdEsuneNb1lpC0Cxsr/9XWFdjvV2u23Ae+yeO5u7N7exravKoJndfHSugUHCV41qG61nWSRx8cl2BHkpXLvDWqP01N+04NurTUVHsAcHIeiDdl+DZCv4hJyRNlwkXAk5x15LLhWJ5DK+CnyFOuFiQcjFmH99GV+zvOdr1FJzYYgZjwyU6A3If1ntSoY1rShDoNDVJFwFuAlwMpMLLkIgI7g8H87U6OhK/cR9Qqf9pS1hlE2P15N4PeBlwBvCx1wKwpvT6M/ED8HTdTCa+I/cz6SrUfJIPMzlRDZ0n9eq3Icf4PvH8o+BOTagBYkGczLQp4NPbyGFBt57hxL8wDeT/PlU+jQ6mpDqiL/+Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID Num", Int64.Type}, {"First name", type text}, {"Last name", type text}, {"Email", type text}, {"Address", type text}, {"Qualification", type text}, {"Qual acquire", type text}, {"Qual expired", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1), #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each let currentID = [ID Num], currentIndex = [Index] in List.Accumulate(Table.SelectRows(#"Added Index", each [ID Num] = currentID and [Index] <=currentIndex)[Index], 0, (state, current) => state + 1)), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Added Custom1", {"Qual expired", "Qual acquire", "Qualification"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom.1", each [Attribute] & " " & Text.From([Custom])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Custom", "Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Value") in #"Pivoted Column"Best Regards,
Dale
2 Replies
Hi adamvainauskas,
Here is the solution of Power Query (M). Please download the demo in the attachment. Please note: many steps can be done through the UI of Query Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZPLTsMwEEV/xcq6ws82YQdSQKKqEAh2URdWO6gWeREniP49M6kDURtYtKuZayvn2jfjLIseUvbYFdEsuneNb1lpC0Cxsr/9XWFdjvV2u23Ae+yeO5u7N7exravKoJndfHSugUHCV41qG61nWSRx8cl2BHkpXLvDWqP01N+04NurTUVHsAcHIeiDdl+DZCv4hJyRNlwkXAk5x15LLhWJ5DK+CnyFOuFiQcjFmH99GV+zvOdr1FJzYYgZjwyU6A3If1ntSoY1rShDoNDVJFwFuAlwMpMLLkIgI7g8H87U6OhK/cR9Qqf9pS1hlE2P15N4PeBlwBvCx1wKwpvT6M/ED8HTdTCa+I/cz6SrUfJIPMzlRDZ0n9eq3Icf4PvH8o+BOTagBYkGczLQp4NPbyGFBt57hxL8wDeT/PlU+jQ6mpDqiL/+Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID Num", Int64.Type}, {"First name", type text}, {"Last name", type text}, {"Email", type text}, {"Address", type text}, {"Qualification", type text}, {"Qual acquire", type text}, {"Qual expired", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1), #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each let currentID = [ID Num], currentIndex = [Index] in List.Accumulate(Table.SelectRows(#"Added Index", each [ID Num] = currentID and [Index] <=currentIndex)[Index], 0, (state, current) => state + 1)), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Added Custom1", {"Qual expired", "Qual acquire", "Qualification"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom.1", each [Attribute] & " " & Text.From([Custom])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Custom", "Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Value") in #"Pivoted Column"Best Regards,
Daleadamvainauskas If you are sure that the number of qualifications will not be more than 4, then you can achieve this by using below DAX as "New Table" (Ofcourse, you can achieve this through Power Query as well with multiple steps of pivot, unpivot and merge...) But it will be a straight forward in DAX assuming the max qualifications will not exceed 4 without having any additional transformations.
We are here adding additional fields to the existing table (Table 1 in your case) and result into a new table.
Test113Out = ADDCOLUMNS(Test113PQ1, "Qual Type 1",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 1"),"Qual",[Qualification]), "Qual Type 1 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 1"),"QualAcquire",[QualAcquired]), "Qual Type 1 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 1"),"QualExpire",[QualExpired]), "Qual Type 2",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 2"),"Qual",[Qualification]), "Qual Type 2 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 2"),"QualAcquire",[QualAcquired]), "Qual Type 2 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 2"),"QualExpire",[QualExpired]), "Qual Type 3",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 3"),"Qual",[Qualification]), "Qual Type 3 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 3"),"QualAcquire",[QualAcquired]), "Qual Type 3 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 3"),"QualExpire",[QualExpired]), "Qual Type 4",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 4"),"Qual",[Qualification]), "Qual Type 4 Acquire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 4"),"QualAcquire",[QualAcquired]), "Qual Type 4 Expire",SELECTCOLUMNS(FILTER(Test113PQ2,Test113PQ1[IDNum]=Test113PQ2[IDNum] && LEFT(Test113PQ2[Qualification],6) = "type 4"),"QualExpire",[QualExpired]) )