How to merge two rows based on a set of conditions in power query

[HeirsPowerBi]

Hello There,

I am trying to merge rows in my based on certain conditions. The table looks like this

Policy_id	Class	Type	sum insured	retention
1	fire	BI	200	350
1	fire	MD	230	350
1	engineering	fs	120	456
2	fire	BI	103	200
2	fire	MD	50	200
2	engineering	fs	25	150

I want to merge the rows where type is MD and BI if they have the same policy_id and the same class.

The merge should be such that the "sum insured " is summed up and one of the values for retention is selected (Same class = same retention hence both would have same retention so i want to just pick one).

The out put should be something like this

Policy_id	Class	Type	sum insured	retention
1	fire	MD & BI	430	350
1	engineering	fs	120	456
2	fire	MD & BI	153	200
2	engineering	fs	25	150

I could really use some help on this as i have been stuck for a while now.

[ronrsnfld]

• Group by Policy and Class
• Aggregate to a single row if TYPE contains MD & BI
• Re-expand the tables.
• Consider what you want if the same policy/class has more than just MD & BI (if that is possible)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrLLEoFUk6eQMLIwABIGpsaKMXqoMj6uoBkjdFlU/PSM/NSU4sy89JBaouBhKERSJGJqRlYkRGaBYYGxlBr0GTBFpgaoEliMd/IFGQMyA2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Policy_id = _t, Class = _t, Type = _t, #"sum insured" = _t, retention = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Policy_id", Int64.Type}, {"Class", type text}, {"Type", type text}, {"sum insured", Int64.Type}, {"retention", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Policy_id", "Class"}, {
        {"Merge Some", each if List.ContainsAll([Type],{"BI","MD"}) 
            then Table.FromColumns({
                {[Policy_id]{0}},
                {[Class]{0}},
                {"MD & BI"},
                {List.Sum([sum insured])},
                {[retention]{0}}},
                {"Policy_id", "Class", "Type", "sum insured", "retention"}) 
            else _}
    }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Policy_id", "Class"}),
    #"Expanded Merge Some" = Table.ExpandTableColumn(#"Removed Columns", "Merge Some", {"Policy_id", "Class", "Type", "sum insured", "retention"}, {"Policy_id", "Class", "Type", "sum insured", "retention"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Merge Some",{{"Policy_id", Int64.Type}, {"Class", type text}, {"Type", type text}, {"sum insured", Int64.Type}, {"retention", Int64.Type}})
                
                
        
in
    #"Changed Type1"

[HeirsPowerBi]

Thanks so much, it is a similar solution to @Payeras_BI 's suggestion

[Payeras_BI]

Hi @HeirsPowerBi ,

See if this helps you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrLLEoFUk6eQMLIwABIGpsaKMXqoMj6uoBkjdFlU/PSM/NSU4sy89JBaouBhKERSJGJqRlYkRGaBYYGxlBr0GTBFpgaoEliMd/IFGQMyA2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Policy_id = _t, Class = _t, Type = _t, #"sum insured" = _t, retention = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Policy_id", Int64.Type}, {"Class", type text}, {"Type", type text}, {"sum insured", Int64.Type}, {"retention", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Policy_id", "Class"}, {{"Type", each Text.Combine(List.Transform(_[Type], Text.From), "&")}, {"Sum insured", each List.Sum([sum insured]), type nullable number}, {"retention", each List.Average([retention]), type nullable number}})
in
    #"Grouped Rows"

[HeirsPowerBi]

Oh yes yes yes.....this works to an extent but there is one more step i cannot figure out.

This merge Should only Happen for Type BI and MD

So if for example there the table is

Policy_id	Class	Type	sum insured	retention
1	fire	BI	200	350
1	fire	MD	230	350
1	Engineering	fs	120	200
1	fire	KM	200	600
1	fire	JP	400	1200

Only the BI and MD types would be merged for the fire class every other thing would remain the same.

So some kind of condition that says if type is MD then merge it with the BI.

i do hope this is possible.

Sorry if my initial question question was not clear.

[Payeras_BI · Accepted solution]

Hi again @HeirsPowerBi ,

In this case I would group only by BI and MD types and, afterwards, append it to the rest.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrLLEoFUk6eQMLIwABIGpsaKMXqoMj6uoBkjdFlXfPSM/NSU4sy89IVQIqLgYShkQHUJDQzvH3hNphhynoFAAkTsKwhFs0Y0rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Policy_id = _t, Class = _t, Type = _t, #"sum insured" = _t, retention = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Policy_id", Int64.Type}, {"Class", type text}, {"Type", type text}, {"sum insured", Int64.Type}, {"retention", Int64.Type}}),
    #"To be grouped" = Table.Group(Table.SelectRows(#"Changed Type", each ([Type] = "BI" or [Type] = "MD")), {"Policy_id", "Class"}, {{"Type", each Text.Combine(List.Transform(_[Type], Text.From), "&")}, {"sum insured", each List.Sum([sum insured]), type nullable number}, {"retention", each List.Average([retention]), type nullable number}}),
    #"Appended Query" = Table.Combine({#"To be grouped", Table.SelectRows(#"Changed Type", each ([Type] <> "BI" and [Type] <> "MD"))})
in
    #"Appended Query"

I hope this helps.

[HeirsPowerBi]

Puuurrrrrrfect....

Thank you this will do just nicely.

I truly appreciate the help