Forum Discussion
Solved
Power Query use Modulo with varying number of rows
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.
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.
2 Replies
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.Thank you so much! This works perfectly.
