PDA

View Full Version : Convert Data from "top to bottom" to "left to right" including some adding values



UK_GER
2017-02-18, 08:50 AM
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
2017-02-18, 12:47 PM
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
2017-02-18, 05:02 PM
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