Forum Discussion

cisyncllc's avatar
cisyncllc
Frequent Visitor
3 years ago
Solved

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.  
  • PaulDBrown's avatar
    PaulDBrown
    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"