Combine date from two tables and split rows over columns

[adamvainauskas]

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	Email	Address
1	444	Paul	Smith	paulsmith@test.com	address001
2	555	John	Doe	email2@test.com	address002
3	333	Jane	Smith	email3@test.com	address003
4	111	Tony	Johnson	email3@test.com	address004
5	222	Derek	Jones	email4@test.com	address005

Table 2

ID Num	First name	Last name	Email	Address	Qualification	Qual acquire	Qual expired
1	Paul	Smith	paulsmith@test.com	address001	type1 Level 1	4/08/2015	31/12/2018
1	Paul	Smith	paulsmith@test.com	address001	type2 Level 2	8/06/2016	31/12/2019
1	Paul	Smith	paulsmith@test.com	address001	type3 level 3	13/04/2017	31/12/2020
2	John	Doe	email2@test.com	address002	type4 level 1	16/02/2018	31/12/2021
2	John	Doe	email2@test.com	address002	type 2 level 3	22/12/2018	31/12/2021
3	Jane	Smith	email3@test.com	address003	type 1 level 4	27/10/2014	31/12/2019
3	Jane	Smith	email3@test.com	address003	type 3 level 2	6/07/2017	31/12/2020
3	Jane	Smith	email3@test.com	address003	type 2 level 1	31/08/2018	31/12/2021
4	Tony	Johnson	email3@test.com	address004	type 2 level 4	11/05/2013	31/12/2018
5	Derek	Jones	email4@test.com	address005	type 1 level 4	16/03/2012	31/12/2018

Table 3

ID Num

ID2 Num

First Name

Last Name

Email

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

paulsmith@test.com

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

email2@test.com

address002

type 2 level 3

22/12/2018

31/12/2021

type4 level 1

16/02/2018

########

3

333

Jane

Smith

email3@test.com

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

email3@test.com

address004

type 2 level 4

11/05/2013

31/12/2018

5

222

Derek

Jones

email4@test.com

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. 

[PattemManohar · Accepted solution]

@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])
          )

[v-jiascu-msft · Accepted solution]

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

[PattemManohar · Accepted solution]

@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])
          )