How to resolve the below error while creating the pivot table by modern activity "create pivot Table"

Help Automation Ops
,
1

Create Pivot Table: A field in your source data has more unique items than can be used in a PivotTable report. Microsoft Excel may not be able to create the report, or may create the report without the data from this field.

this is the error

An error message indicates that a field in the source data has more unique items than can be used in a PivotTable report in Microsoft Excel. (Captioned by AI)
An error message indicates that a field in the source data has more unique items than can be used in a PivotTable report in Microsoft Excel. (Captioned by AI)934×616 44.7 KB

2

Hi @anjani_priya

In PivotTables, the practical limit for unique items in a single field is much lower so that might be the issue

Check on this thread

Hope this helps :innocent:

3

But I have 70 columns only

4

Because of the single column, its getting error. But I need that column

5

Can you try manually creating pivot in the excel itself?

6

yes i did. Its creating

7

@anjani_priya,

Just get all the columns manually and check which one is duplicate and work around that.
Excel would have some background logic which allows the duplicate columns into Pivot but, with UiPath, we will have to add that logic explicitly.

8

In one column ,there are more unique values. How to handle it

9

Can you try this invoke code
Its LLM generated please change the values accordingly

Sub CreatePivotTable()

    Dim wsSource As Worksheet
    Dim wsPivot As Worksheet
    Dim pCache As PivotCache
    Dim pTable As PivotTable
    Dim lastRow As Long
    Dim lastCol As Long
    Dim dataRange As Range

    ' Set references
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Source data sheet
    Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsSource)
    wsPivot.Name = "PivotSheet"

    ' Find last row and column in source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

    ' Define the data range
    Set dataRange = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))

    ' Create Pivot Cache
    Set pCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)

    ' Create Pivot Table
    Set pTable = pCache.CreatePivotTable(TableDestination:=wsPivot.Range("A3"), TableName:="MyPivotTable")

    ' Add Fields
    With pTable
        .PivotFields("Category").Orientation = xlRowField
        With .PivotFields("Amount")
            .Orientation = xlDataField
            .Function = xlSum
        End With
    End With

End Sub

Hope this helps!

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.