Need Help with cross Tab table

[cisyncllc]

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.

[cisyncllc]

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]

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 · Accepted solution]

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]

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"

[PaulDBrown]

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...)

[cisyncllc]

[amitchandak]

@cisyncllc , Matrix visual

Id on row, Group Desc of COlumn

and max of Itemdesc on value

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual