Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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-...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

     

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi BA_Pete  thanks.

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

    • BA_Pete's avatar
      BA_Pete
      Super User
      4 years ago

      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