Help Merging Two Tables

[LoopingBI]

I am trying to merge two tables in Power Query that I am having difficulty with.

I have one table that lists serial numbers and parts related issues.

Serial Number	Part Cat 1	Part Cat 2	Part Cat 3	Service Cat 1	Service Cat 2	Service Cat 3
10001	x	x	x
10001	x	x	x
10002	x	x	x
10002	x	x	x
10003	x	x	x
10006	x	x	x

I have another table that lists serial numbers and service related issues. 

Serial Number	Part Cat 1	Part Cat 2	Part Cat 3	Service Cat 1	Service Cat 2	Service Cat 3
10001				x	x	x
10003				x	x	x
10003				x	x	x
10003				x	x	x
10004				x	x	x
10004				x	x	x
10005				x	x	x
10006				x	x	x

I am trying to merge the tables to look like this:

Serial Number	Part Cat 1	Part Cat 2	Part Cat 3	Service Cat 1	Service Cat 2	Service Cat 3
10001	x	x	x	x	x	x
10001	x	x	x
10002	x	x	x
10002	x	x	x
10003	x	x	x	x	x	x
10003				x	x	x
10003				x	x	x
10004				x	x	x
10004				x	x	x
10005				x	x	x
10006	x	x	x	x	x	x

The final table needs to have all the serial numbers from both reports (they can be duplicated). If a serial number has both a part and a service issue, then that part and service issue should appear on the same row. If a serial number has a different number of parts or service issues, it should be creating additional rows with the serial number to fit them in. For example, a serial number can have 1 part issue and 3 service issues, so the final table should have 3 rows for the serial number. The first row will have the one part issue and the first service issue. The next two rows should have blanks for the part issues, but list the second and third service issues.

Thank you in advance for any help!

[dufoq3]

Hi @LoopingBI, different solution:

Replace Table1 and Table2 with your table references (if you don't know how, read note below my post).

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBU0lGqQMIKcByrQ6wKIyqoMCaowgy3ilgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Part Cat 1" = _t, #"Part Cat 2" = _t, #"Part Cat 3" = _t, #"Service Cat 1" = _t, #"Service Cat 2" = _t, #"Service Cat 3" = _t]),
    // You can probably delete this step
    ReplaceBlankToNull1 = Table.TransformColumns(Table1, {}, each if Text.Trim(_) = "" then null else _),
    AddedIndex1 = Table.AddIndexColumn(ReplaceBlankToNull1, "Index", 0, 1, Int64.Type),
    Table1RowCount = Table.RowCount(AddedIndex1),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBU0lFSQMIVcByrA1FhTBcVJlRQYUpQhRluFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Part Cat 1" = _t, #"Part Cat 2" = _t, #"Part Cat 3" = _t, #"Service Cat 1" = _t, #"Service Cat 2" = _t, #"Service Cat 3" = _t]),
    // You can probably delete this step
    ReplaceBlankToNull2 = Table.TransformColumns(Table2, {}, each if Text.Trim(_) = "" then null else _),
    AddedIndex2 = Table.AddIndexColumn(ReplaceBlankToNull2, "Index", Table1RowCount, 1, Int64.Type),
    AppendedQuery = Table.Combine({AddedIndex1, AddedIndex2}),
    SortedRows = Table.Sort(AppendedQuery,{{"Serial Number", Order.Ascending}, {"Index", Order.Ascending}}),

    Fn_Transform = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[#"Serial Number"="10002"]}[Fn],
            _Detail = myTable,
            _SplitTable = Table.SplitAt(_Detail, List.Count(List.Select(_Detail[Index], (x)=> x < Table1RowCount))),
            _ToRowsZipped = List.Zip(List.Transform(_SplitTable, (x)=> Table.ToRows(x))),
            _ToTable = List.Transform(_ToRowsZipped, (x)=> 
                [ a = Table.FromRows(List.RemoveNulls(x), Value.Type(_Detail)),
                b = Table.FillUp(a, Table.ColumnNames(a)),
                c = Table.FirstN(b, 1)
                ][c]),
            _Combined = Table.Combine(_ToTable)
        ][_Combined],

    GroupedRows = Table.Group(SortedRows, {"Serial Number"}, {{"Fn", Fn_Transform, type table}}),
    Combined = Table.Combine(GroupedRows[Fn]),
    RemovedColumns = Table.RemoveColumns(Combined,{"Index"})
in
    RemovedColumns

[slorin]

Hi @LoopingBI 

let
TableA_with_index = Table.Combine(
     Table.Group(TableA, {"Serial Number"}, {{"Data", each Table.AddIndexColumn(_,"Index")}}) [Data]),
TableB_with_index = Table.Combine(
     Table.Group(TableB, {"Serial Number"}, {{"Data", each Table.AddIndexColumn(_,"Index")}}) [Data]),
Combine = Table.Combine({TableA_with_index, TableB_with_index}),
UnPivot = Table.UnpivotOtherColumns(Combine, {"Serial Number", "Index"}, "Attribute", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[Attribute]), "Attribute", "Value"),
Remove_Index = Table.RemoveColumns(Pivot,{"Index"})
in
Remove_Index

Stéphane 

[LoopingBI]

Sorry for the delayed response. It took me a while to apply this solution to my problem. However, I did come across an issue.

When pivoting, I get the following error for certain records:
Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
[List]

This is how my table looks during the unpivot step:

Serial Number	Index	Attribute	Value
1001	0	Part Cat 1
1001	0	Part Cat 1	x
1001	0	Comments
1001	0	Comments	x
1001	0	Service Cat 1	x
1001	0	Service Cat 1
1001	0	Service Cat 2
1001	0	Service Cat 2	x
1001	0	Service Cat 3
1001	0	Service Cat 3	x
1001	0	Part Cat 2	x
1001	0	Part Cat 2
1001	0	Part Cat 3
1001	0	Part Cat 3	x
1002	0	Part Cat 1
1002	0	Part Cat 1	x
1002	0	Comments
1002	0	Comments	x
1002	0	Service Cat 1	x
1002	0	Service Cat 1
1002	0	Service Cat 2	x
1002	0	Service Cat 2
1002	0	Service Cat 3	x
1002	0	Service Cat 3
1002	0	Part Cat 2
1002	0	Part Cat 2	x
1002	0	Part Cat 3	x
1002	0	Part Cat 3

It looks like if a serial number appeared in Table 1 with a part issue and also appeared in Table 2 with a service issue, then it will have issues pivoting. I think an additional index can resolve this somehow.

[slorin]

Hi @LoopingBI 

May be with List.First in pivot step ?

or Text.Combine ?

let
TableA_with_index = Table.Combine(
     Table.Group(TableA, {"Serial Number"}, {{"Data", each Table.AddIndexColumn(_,"Index")}}) [Data]),
TableB_with_index = Table.Combine(
     Table.Group(TableB, {"Serial Number"}, {{"Data", each Table.AddIndexColumn(_,"Index")}}) [Data]),
Combine = Table.Combine({TableA_with_index, TableB_with_index}),
UnPivot = Table.UnpivotOtherColumns(Combine, {"Serial Number", "Index"}, "Attribute", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[Attribute]), "Attribute", "Value", List.First),
Remove_Index = Table.RemoveColumns(Pivot,{"Index"})
in
Remove_Index

 Stéphane