Forum Discussion
Need Help with cross Tab table
- 3 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"
Thank you, however I need this as a table to use in power query. Is there a way to do this in power query?
- PaulDBrown3 years agoCommunity Champion
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"- cisyncllc3 years agoFrequent Visitor
thank you so much, however I am getting a small issue when I am using my actual source data. please see code below:
let
Source = lwmodop,
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"lwmainid", Int64.Type}, {"groupdesc", type text}, {"itemdesc", type text}}
),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type", {"groupdesc", "itemdesc", "lwmainid"}),
#"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",
"itemdesc"
),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"mogroup", "moitem", "lwmodopid", "addtime", "adduser", "mobilepkey", "origmfrrec", "ismfrrec"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([lwmainid] = 106))
in
#"Filtered Rows"- cisyncllc3 years agoFrequent Visitor