Un-Pivot or Custom Column

[Anonymous]

Hello everybody,

again I need some help. I have following Table:

Ordernumber	Information
123	A
123	B
123	C
456	A1
456	C2
789	D

I want to "move" the row-values of column Information to individual columns for the first,second,third value of the corresponding Ordernumber. Like the table below:

Ordernumber	Information1	Information2	Information3
123	A	B	C
456	A1	C2
789	D

I can be sure that there is a max amount of rows per Ordernumber (in this example 3)

I cant get it to work with un-pivot, but maybe I could create 3 custom-columns, get TopN values and remove duplicates.

But i didn't figure it out yet.

Looking forward to your help.

[BA_Pete]

Hi @Anonymous ,

Maybe not the cleanest way, but very easy to see in steps. Paste this over the default code in blank query Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRclSK1YGxnZDYzmC2iakZSI0hEsfZCMwxt7AEclyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ordernumber  " = _t, #"Information  " = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Ordernumber  ", Int64.Type}, {"Information  ", type text}}),
    groupRows = Table.Group(chgTypes, {"Ordernumber  "}, {{"data", each _, type table [#"Ordernumber  "=nullable number, #"Information  "=nullable text]}}),
    addNestedIndex = Table.TransformColumns(groupRows, {"data", (i) => Table.AddIndexColumn(i, "Index", 1, 1)}),
    expandNestedData = Table.ExpandTableColumn(addNestedIndex, "data", {"Information  ", "Index"}, {"Information  ", "Index"}),
    addInfoPrefix = Table.TransformColumns(expandNestedData, {{"Index", each "Information " & Text.From(_, "en-GB"), type text}}),
    pivotInfoXCol = Table.Pivot(addInfoPrefix, List.Distinct(addInfoPrefix[Index]), "Index", "Information  ")
in
    pivotInfoXCol

SUMMARY:

1) Grouped data on [Ordernumber] and set aggregation to All Rows to nest tables by [Ordernumber]

2) Added custom step to apply an index column to each nested table

3) Expanded nested tables back out to data rows, now including [Index] column

4) Used Transform > Format > Prefix to add "Information " to the [Index] column to serve as new column headers

5) Pivot [Index] column, using [Information] column as Values, and setting 'Do Not Aggregate' under Advanced Options

This gives me the following output:

Pete

[Anonymous]

Hi @BA_Pete  thanks.

Looking good as well, althought i will need some time to completely undertsand what you did there 😁

[BA_Pete]

No worries.

I was typing this up when you posted your own solution, so didn't realise you'd solved until I'd posted mine and refreshed page.

Didn't mean to cause confusion 🙂

Pete

[Anonymous · Accepted solution]

Provided the solution myself:

Create a custom function, that returns the Information for each Ordernumber (see below)

After that you can invoke this function from the base table, to extract the return-values as new column. 

After that, you Split the column by deliter => done

(Paramter as number) =>

let
    Source = Table.SelectRows(
    Tabelle1, each ([Ordernumber]) = Paramter
    )[Information]
in
    Source