Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
Proud to be a Datanaut!
Hi @BA_Pete thanks.
Looking good as well, althought i will need some time to completely undertsand what you did there 😁
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
Proud to be a Datanaut!
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