Forum Discussion

cisyncllc's avatar
cisyncllc
Frequent Visitor
4 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.

 

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

7 Replies

  • cisyncllc's avatar
    cisyncllc
    Frequent Visitor
    4 years ago

    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.  

  • cisyncllc's avatar
    cisyncllc
    Frequent Visitor
    4 years ago

    Thank you, however I need this as a table to use in power query.  Is there a way to do this in power query?

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      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's avatar
        cisyncllc
        Frequent Visitor
        4 years ago

        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"