Power Query use Modulo with varying number of rows

[jhammond]

My end-game is to have one row per ClaimNo, with all the relevant information from the 3 or 4 rows combined into one.  I would use modulo, except the data can either be on 3 rows or 4 rows, so I'm not sure to handle that.  Any help or other ideas would be greatly appreciated.

Combined Data SAMPLE for Forum 

[v-jingzhang · Accepted solution]

Hi @jhammond 

Here are my transformation steps. You can also download the pbix file attached at bottom to see details. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVFdS8MwFP0roa9mbPls6ltREcHhUEFw7CFdgg2k7UiyQf+9t+pqZx+EC+fkknPuucl2mxW5ZFSoDGelMcHGCExxIdHzserRbXAnC53vKj9smwAJk1IOrR2eGty41OOXpJPF7+4AjTcbk9etwWj9gLgiSoxWm867fQ9ECjF32tRdO4x9SrUN10gQucgZoaP61YYGgC/JaklXlKIFOnM2d7trtPOAbZeQPgHXlf9dagh8HLZ+1IdozVmu8oJRLqdRCy4F539DEPozmUCK8UDnPv/fn9aldgxZ7hP8CLrvvLla26ayIdZfb30p3n0C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClaimNo = _t, Address = _t, #"City,State,Zip" = _t, Phone = _t, Email = _t, Agent = _t, Policy = _t, Term = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClaimNo", Int64.Type}, {"Address", type text}, {"City,State,Zip", type text}, {"Phone", type text}, {"Email", type text}, {"Agent", type text}, {"Policy", type text}, {"Term", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ClaimNo", "Address", "City,State,Zip"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Address", "Attribute"}, {"City,State,Zip", "Value"}}),
    Custom1 = Table.SelectColumns(#"Changed Type",{"ClaimNo", "Agent", "Policy"}),
    #"Renamed Columns1" = Table.RenameColumns(Custom1,{{"Agent", "Attribute"}, {"Policy", "Value"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    #"Removed Duplicates" = Table.Distinct(#"Appended Query"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Attribute] <> null and [Attribute] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[jhammond]

Thank you so much!  This works perfectly.