CODEWITHSUNDEEP
powerbi
Ven
Ven

Reputation: 2014

Pivot without Aggregation-Power BI

I have used power query from previous post, to convert JSON array to PowerBI data. Pivoting a column is throwing error on transformation

Error: Expression.Error: There were too many elements in the enumeration to complete the operation. Details: List

{
  "Data": [
    {
      "Type": "Workers",
      "Values": [
        "Manager",
        "Delegate",
        "Authority"
      ]
    },
    {
      "Type": "WorkCode",
      "Values": [
        "134",
        "135",
        "140",
        "141",
        "142",
        "143",
        "150"
      ]
    },
    {
      "Type": "Place",
      "Values": [
        "UK",
        "Europe"
      ]
    }
  ]
}

Expected Output: 

Workers      WorkCode    Place
-----------------------
Manager       134         UK
delegate      135         Europe 
Authority     etc

Query:

let
    Source = Json.Document(Web.Contents("http://127.0.0.1:8083/api/v1/rthb-e-powerBI-client-login-report/refData")),
   #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Type", "Values"}, {"Value.Type", "Value.Values"}),
    #"Expanded Value.Values" = Table.ExpandListColumn(#"Expanded Value1", "Value.Values"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value.Values",{"Name"})

in
    #"Removed Columns"

Appreciate any inputs

Upvotes: 1

Views: 1565

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

There is no relationship between "Workers", "WorkCode" and "Place" in the JSON file specified, thus the error. One can assume that a specific worker code belongs to a specific worker based on position.

To get the desired result in Power Bi the relationship needs to be established between Workers their codes and places. It can be done as follows:

1) Add an Index column (say "Index") enter image description here

2) Group rows by Workers enter image description here

3) Add another index column (subindex) 

= Table.AddColumn(#"Grouped Rows", "subindex", each Table.AddIndexColumn([codes], "wcode", 1,1))

4) Can remove unwanted column ("codes" in this example), now

5) Expand "Subindex" column enter image description here

6) Now, columns "colname", "subindex.Index" can be removed. And the final result should be similar to this one enter image description here

7) Pivot the table enter image description here

Here is the complete Power Query code

let
    Source = Json.Document(File.Contents("<<PATH TO YOUR JSON FILE>>")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Type", "Values"}, {"Value.Type", "Value.Values"}),
    #"Expanded Value.Values" = Table.ExpandListColumn(#"Expanded Value1", "Value.Values"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Value.Values",{{"Value.Type", "colname"}, {"Value.Values", "colvalue"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"colname"}, {{"codes", each _, type table [colname=text, colvalue=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "subindex", each Table.AddIndexColumn([codes], "wcode", 1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"codes"}),
    #"Expanded subindex" = Table.ExpandTableColumn(#"Removed Columns1", "subindex", {"colname", "colvalue", "Index", "wcode"}, {"subindex.colname", "subindex.colvalue", "subindex.Index", "subindex.wcode"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded subindex",{"colname", "subindex.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[subindex.colname]), "subindex.colname", "subindex.colvalue")
in
    #"Pivoted Column"

Upvotes: 3

Related Questions