Merge two rows based on multiple column values

[skutovicsakos]

Hi all

The simplified version of the table looks like this:

And from that, I need something like the picture below, IF two rows' ID, Field and Type fields are the same, they should be merged while creating 2 new columns. (ones without a match, should be discarded)

[Jimmy801 · Accepted solution]

Hello @skutovicsakos ,

check out this solution. It works with grouping and pivoting and its not dynamic. If only one row its found its deleted, with 2 rows works fine, if 3 rows are found they are deleted as well, because the program is not able to handle it.

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4YKCnFQg7QHEboZGxkqxOhDJJFySRlCdSYl5QAhk+AMxikx+UWJeeipUxtBU18QcLp2ErhEqHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Point = _t, Field = _t, Type = _t, Value = _t]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Point", type text}, {"Field", type text}, {"Type", type text}, {"Value", type text}}),
    PivotTable = (tTable as table) =>
    let
        DeleteNotNeededColumns = Table.SelectColumns
        (
            tTable,
            {"Point", "Value"}
        ),
        AddIndex = Table.AddIndexColumn
        (
            DeleteNotNeededColumns, 
            "Index", 
            1, 
            1
        ),
        CreateRecordColumn = Table.AddColumn
        (
            AddIndex, 
            "Complete Row", 
            each _
        ),
        KeepIndexAndCompleteColumn = Table.SelectColumns
        (
            CreateRecordColumn,
            {"Index", "Complete Row"}
        ),
        PivotColumn = Table.Pivot
        (
            Table.TransformColumnTypes
            (
                KeepIndexAndCompleteColumn, 
                {{"Index", type text}}, 
                "de-DE"
            ), 
            List.Distinct
            (
                Table.TransformColumnTypes
                (
                    KeepIndexAndCompleteColumn, 
                    {{"Index", type text}}, 
                    "de-DE"
                )[Index]
            ), 
            "Index", 
            "Complete Row"
        ),
        ExpandFirstColumn = Table.ExpandRecordColumn
        (
            PivotColumn,
            "1",
            {"Point", "Value"}, 
            {"1.Point", "1.Value"}
        ),
        ExpandSecondColumn = Table.ExpandRecordColumn
        (
            ExpandFirstColumn, 
            "2", 
            {"Point", "Value"}, 
            {"2.Point", "2.Value"}
        )
    in
        ExpandSecondColumn,




    GroupTable = Table.Group
    (
        #"Geänderter Typ" ,
        {"ID", "Field", "Type"}, 
        {
            {"AllRows", each _, type table [ID=number, Point=text, Field=text, Type=text, Value=text]}, 
            {"RowCount", each Table.RowCount(_), type number}
        }
    ),
    FilterTable = Table.SelectRows
    (
        GroupTable, 
        each ([RowCount] = 2)
    ),
    ApplyCustomFunction = Table.AddColumn
    (
        FilterTable,
        "NewColumn",
        each PivotTable([AllRows])
    
    ),
    DeleteColumns = Table.RemoveColumns(ApplyCustomFunction,{"AllRows", "RowCount"}),
    ExpandColumns = Table.ExpandTableColumn(DeleteColumns, "NewColumn", {"1.Point", "1.Value", "2.Point", "2.Value"}, {"1.Point", "1.Value", "2.Point", "2.Value"})
in
    ExpandColumns

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous · Accepted solution]

The solution below will not require a custom function.

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4YKCnFQg7QHEboZGxkqxOhDJJFySRlCdSYl5QAhk+AMxikx+UWJeeipUxtBU18QcLp2ErhEqHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Point = _t, Field = _t, Type = _t, Value = _t]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Point", type text}, {"Field", type text}, {"Type", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Geänderter Typ", {"ID", "Field", "Type"}, {
 {"Point", each List.First([Point]), type text},  {"Value", each List.First([Value]), type text},
{"AllData", each _, type table [ID=number, Point=text, Field=text, Type=text, Value=text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each Table.RowCount([AllData])=2 ),
    Transform = Table.TransformColumns(#"Filtered Rows",{{"AllData", each _{1}, type record}}),
    #"Expanded AllData" = Table.ExpandRecordColumn(Transform, "AllData", {"Point", "Value"}, {"D_Point", "D_Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"ID", "Point", "Field", "Type", "Value", "D_Point", "D_Value"})
in
    #"Reordered Columns"

[Jimmy801]

Hello @skutovicsakos 

what should happen if 3 rows are found?

BR

Jimmy

[skutovicsakos]

Hello @Jimmy801 

Physically there are no more than 3 rows with those values.

Also, after the tranformation the Point column's value should be always "a" and D_Point  "b".

[Jimmy801]

Hello
are there any news on this topic? Did solve or help any reply your problem?
If this is the case, please mark it as solution.

Jimmy

[Jimmy801 · Accepted solution]

Hello @skutovicsakos ,

check out this solution. It works with grouping and pivoting and its not dynamic. If only one row its found its deleted, with 2 rows works fine, if 3 rows are found they are deleted as well, because the program is not able to handle it.

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4YKCnFQg7QHEboZGxkqxOhDJJFySRlCdSYl5QAhk+AMxikx+UWJeeipUxtBU18QcLp2ErhEqHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Point = _t, Field = _t, Type = _t, Value = _t]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Point", type text}, {"Field", type text}, {"Type", type text}, {"Value", type text}}),
    PivotTable = (tTable as table) =>
    let
        DeleteNotNeededColumns = Table.SelectColumns
        (
            tTable,
            {"Point", "Value"}
        ),
        AddIndex = Table.AddIndexColumn
        (
            DeleteNotNeededColumns, 
            "Index", 
            1, 
            1
        ),
        CreateRecordColumn = Table.AddColumn
        (
            AddIndex, 
            "Complete Row", 
            each _
        ),
        KeepIndexAndCompleteColumn = Table.SelectColumns
        (
            CreateRecordColumn,
            {"Index", "Complete Row"}
        ),
        PivotColumn = Table.Pivot
        (
            Table.TransformColumnTypes
            (
                KeepIndexAndCompleteColumn, 
                {{"Index", type text}}, 
                "de-DE"
            ), 
            List.Distinct
            (
                Table.TransformColumnTypes
                (
                    KeepIndexAndCompleteColumn, 
                    {{"Index", type text}}, 
                    "de-DE"
                )[Index]
            ), 
            "Index", 
            "Complete Row"
        ),
        ExpandFirstColumn = Table.ExpandRecordColumn
        (
            PivotColumn,
            "1",
            {"Point", "Value"}, 
            {"1.Point", "1.Value"}
        ),
        ExpandSecondColumn = Table.ExpandRecordColumn
        (
            ExpandFirstColumn, 
            "2", 
            {"Point", "Value"}, 
            {"2.Point", "2.Value"}
        )
    in
        ExpandSecondColumn,




    GroupTable = Table.Group
    (
        #"Geänderter Typ" ,
        {"ID", "Field", "Type"}, 
        {
            {"AllRows", each _, type table [ID=number, Point=text, Field=text, Type=text, Value=text]}, 
            {"RowCount", each Table.RowCount(_), type number}
        }
    ),
    FilterTable = Table.SelectRows
    (
        GroupTable, 
        each ([RowCount] = 2)
    ),
    ApplyCustomFunction = Table.AddColumn
    (
        FilterTable,
        "NewColumn",
        each PivotTable([AllRows])
    
    ),
    DeleteColumns = Table.RemoveColumns(ApplyCustomFunction,{"AllRows", "RowCount"}),
    ExpandColumns = Table.ExpandTableColumn(DeleteColumns, "NewColumn", {"1.Point", "1.Value", "2.Point", "2.Value"}, {"1.Point", "1.Value", "2.Point", "2.Value"})
in
    ExpandColumns

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy