Forum Discussion
Un-Pivot or Custom Column
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.
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
4 Replies
- BA_Pete
Super User
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 pivotInfoXColSUMMARY:
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
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
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
