Forum Discussion
cisyncllc
4 years agoFrequent Visitor
Need Help with cross Tab table
I need help changing a table from multiple rows to a grouped by and then into a single row see example. I need to be able to do this in Power Query.
- 4 years ago
Assuming the row structure will always be repeated,
Try:
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "fZHRasMwDEV/Rfg5MNLB3l1ZSbwkkpGdZqX0/39j2kq7mC71o3x0L/fqcnFt2368u8blJSfCAh5LFN4MlGY5UYCkkuAN0OfBXZu/xZnKIAGkA+KiZ5uwQCeKVGFJIpct1Xv1PYFp60A+QBDRaoNOMRCbjC1HLItS3v1XE887fl+x3Ox6/8rOI8qcpohm07iFR5aVf4nDz/uvouOiUz95i7PFngtZIwdZK+ipjk6FS4Vs8h1VRmIwr5z3GBUcdyxuDTwS34E68aeQlWJHu34D", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, groupdesc = _t, itemdescp = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"ID", Int64.Type}, {"groupdesc", type text}, {"itemdescp", type text}} ), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type", {"groupdesc", "itemdescp", "ID"}), #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 1, 1, Int64.Type), #"Inserted Modulo" = Table.AddColumn( #"Added Index", "Modulo", each Number.Mod([Index], 7), type number ), #"Added Conditional Column" = Table.AddColumn( #"Inserted Modulo", "Custom", each if [Modulo] = 5 then "EVIDENCE 1" else [groupdesc] ), #"Removed Columns" = Table.RemoveColumns( #"Added Conditional Column", {"groupdesc", "Index", "Modulo"} ), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Custom", "GroupDesc"}}), #"Pivoted Column" = Table.Pivot( #"Renamed Columns", List.Distinct(#"Renamed Columns"[GroupDesc]), "GroupDesc", "itemdescp" ) in #"Pivoted Column"
cisyncllc
3 years agoFrequent Visitor
I was abel to take your code an tweak it with the fields I needed and it worked perfectly. Thank you for your insight and knowledge.