Forum Discussion
matheus_peppers
3 years agoFrequent Visitor
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, b...
- 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"
ImkeF
3 years agoCommunity Champion
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"