Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HeirsPowerBi
Helper I
Helper I

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

Hello There,

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

Policy_idClassTypesum insuredretention
1fireBI200350
1fireMD230350
1engineeringfs120456
2fireBI103200
2fireMD50200
2engineeringfs25150

 

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_idClassTypesum insuredretention
1fireMD & BI430350
1engineeringfs120456
2fireMD & BI153200
2engineeringfs25150

 

 

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

1 ACCEPTED SOLUTION

Hi again @HeirsPowerBi ,

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

Payeras_BI_0-1656149358855.png

 

Payeras_BI_1-1656149415226.png

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.

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

6 REPLIES 6
ronrsnfld
Super User
Super User

  • 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)

ronrsnfld_0-1656180924434.png

 

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"

ronrsnfld_1-1656180985887.png

 

 

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

Payeras_BI
Super User
Super User

Hi @HeirsPowerBi ,

See if this helps you:

Payeras_BI_1-1656080104033.png

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"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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_idClassTypesum insuredretention
1fireBI200350
1fireMD230350
1Engineering fs120200
1fireKM200600
1fireJP4001200
     

 

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.

Hi again @HeirsPowerBi ,

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

Payeras_BI_0-1656149358855.png

 

Payeras_BI_1-1656149415226.png

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.

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Puuurrrrrrfect....

Thank you this will do just nicely.

 

I truly appreciate the help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors