Convert Data from "top to bottom" to "left to right" including some adding values

[UK_GER]

Convert Data from "top to bottom" to "left to right" including some adding values

Hi together,

I have a very long table of data with:

- Order No
- Year
- bocked cost

Pls. see this example:

let
Quelle = {"0001|2010|20","0001|2011|30","0001|2012|50","0001|2013|10","0002|2009|15","0002|2011|25","0002|2013|35"},
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitTextByDelimiter("|"), null, null, ExtraValues.Error),
#"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Order No"}, {"Column2", "Year"}, {"Column3", "booked cost"}}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Order No", Int64.Type}, {"Year", Int64.Type}, {"booked cost", Currency.Type}})
in
#"Geänderter Typ"

Orders might have been booked in some years, but must not have been booked in all years.
Also the orders have different starting end ending years of booking.


I would like to have the data in the following table format:

let
Quelle = {"0001|0|20|30|50|10","0002|15|0|25|0|35"},
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitTextByDelimiter("|"), null, null, ExtraValues.Error),
#"Geänderter Typ" = Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Column1", "Order No"}, {"Column2", "2009"}, {"Column3", "2010"}, {"Column4", "2011"}, {"Column5", "2012"}, {"Column6", "2013"}})

in
#"Umbenannte Spalten"


So from the idea:

Take table 1. Find out the lowest and highest year off booking. Add years with no bookings on an Order No incl. a zero.
Somehow "transpose?/reorder?" the table.
Put the headlines like: 1st: Order no; 2nd lowest year; 3rd lowest year +1, ....

Goal is to see Order No "row by row" including all years and related bookings. and add sparklines behind.


I did some trails with transpose and unpivot and... but up to now could not find a solution.


If anybody has an idea or a hint, please let me know.

Thanks
Uwe

 

[Bill Szysz]

Hi
Check this code below.
Is that what you want?

let
Quelle = {"0001|2010|20","0001|2011|30","0001|2012|50","0001|2013|10","0002|2009|15","0002|2011|25","0002|2013|35", "0003|2006|15", "0003|2011|22"},
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitTextByDelimiter("|"), null, null, ExtraValues.Error),
#"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Order No"}, {"Column2", "Year"}, {"Column3", "booked cost"}}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Order No", Int64.Type}, {"Year", Int64.Type}, {"booked cost", Currency.Type}}),
    EmptyTable = Table.FromRows({},{"Order No"}&List.Transform({List.Min(#"Geänderter Typ"[Year])..List.Max(#"Geänderter Typ"[Year])}, each Text.From(_))),
    #"Changed Type" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Year", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Year]), "Year", "booked cost"),
    Combine = Table.Combine({EmptyTable, #"Pivoted Column"})
in
    Combine

Regards

 

[UK_GER]

Hi Bill,

yes, yes, yes ;-)
That is what I was looking for.

Many, many thanks!!!

No sunshine here the whole day - but thanks to you only outside.

Best Regards to PL