Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.