Forum Discussion
Need Help with cross Tab table
- 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"
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 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"
- cisyncllc4 years agoFrequent Visitor
- PaulDBrown4 years agoCommunity Champion
The problem is that you have many more fields than the sample data you posted. There is also an issue in the sample data which needs to be solved: you have two rows where the value is "Evidence" in both, and you cannot have two columns with the same name (hence the importance of having the same row structure...)