Forum Discussion

matheus_peppers's avatar
matheus_peppers
Frequent Visitor
3 years ago
Solved

Error when pivoting table

Hello, everyone. What's up?

I'm trying to make a pivot in my table that looks like this:

 

 

If you notice, the values that should be columns are repeated, because I added several tables, because I need a list view of all tables.

However, when I pivot, the following error occurs:

 

 

 

Can anyone help me by telling me what it could be, or what I can do to fix it and leave the list the way I want it, which is leaving the header at the top and the sequence of values at the bottom?

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi matheus_peppers ,
    you need to create an additional column that creates a row number for the future table.
    That will be calculated by adding an index column and applying a "Number.IntegerDivide"-operation on it with the number of of pivot column (rows for each batch).
    Please paste the following code into the advanced editor and follow the steps:

     

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "PcunFQAwCAXAXb6OSC8ybQoe+68RgsCdOSJMOASwIyxRVG1RUh1RVl1RUf1RbTQb3cawETyYHw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t, Column2 = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(
        #"Added Index",
        {{"Index", each Number.IntegerDivide(_, 5), Int64.Type}}
    ),
    #"Pivoted Column" = Table.Pivot(
        #"Integer-Divided Column",
        List.Distinct(#"Integer-Divided Column"[Column1]),
        "Column1",
        "Column2"
    )
in
    #"Pivoted Column"

     

     

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi matheus_peppers ,
    you need to create an additional column that creates a row number for the future table.
    That will be calculated by adding an index column and applying a "Number.IntegerDivide"-operation on it with the number of of pivot column (rows for each batch).
    Please paste the following code into the advanced editor and follow the steps:

     

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "PcunFQAwCAXAXb6OSC8ybQoe+68RgsCdOSJMOASwIyxRVG1RUh1RVl1RUf1RbTQb3cawETyYHw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t, Column2 = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(
        #"Added Index",
        {{"Index", each Number.IntegerDivide(_, 5), Int64.Type}}
    ),
    #"Pivoted Column" = Table.Pivot(
        #"Integer-Divided Column",
        List.Distinct(#"Integer-Divided Column"[Column1]),
        "Column1",
        "Column2"
    )
in
    #"Pivoted Column"