Transposing part of row data to colomns

[Timo1980]

Hi All,

I trying to figure out how the following can be done in power query. With Dax im able to achieve with the earlier function, however my data set very large (millions)  that I would rather use power query to limit the rows in the model.

My Data set looks like below (1) and i would like to add 2 colomns for with volume Previous Year and Sales PY (containing 2019 data) and only keeping the rows of 2020 data and have the 2019 data in new colomns (2)

[Anonymous · Accepted solution]

    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY67DYAwEEN3SU0RfJ8kJayB0gEzANNzh5AIUNiNLT9PU0DsS+jCth/mg6kHmbMAoXaffDSB2JwSK7eFeVnvAboGkEj/uQ9oEgcoCd0FxNcDUScIRZR/wRdySc4Rzm3ePLDAOPwGPA9wPcjmtZ4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, customer = _t, prod = _t, vol = _t, sales = _t]),
    #"Merge di colonne" = Table.CombineColumns(Origine,{"sales", "vol"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"vs"),
    #"Colonna trasformata tramite Pivot" = Table.Pivot(#"Merge di colonne", List.Distinct(#"Merge di colonne"[Year]), "Year", "vs"),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Colonna trasformata tramite Pivot", "2019", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"2019-sales", "2019-value"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"customer", type text}, {"prod", type text}, {"2019-sales", Int64.Type}, {"2019-value", Int64.Type}}),
    #"Suddividi colonna in base al delimitatore1" = Table.SplitColumn(#"Modificato tipo", "2020", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"2020-sales", "2020-value"}),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore1",{{"2020-sales", Int64.Type}, {"2020-value", Int64.Type}})
in
    #"Modificato tipo1"

[Vera_33 · Accepted solution]

Hi @Timo1980 

Here is another way, paste the code in Advanced Editor of a blank query. The vCurYear you can change the year, now it is 2020

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY67DYAwEEN3SU0RfJ8kJayB0gEzANNzh5AIUNiNLT9PU0DsS+jCth/mg6kHmbMAoXaffDSB2JwSK7eFeVnvAboGkEj/uQ9oEgcoCd0FxNcDUScIRZR/wRdySc4Rzm3ePLDAOPwGPA9wPcjmtZ4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, customer = _t, prod = _t, vol = _t, sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
vCurYear = 2020,
PY = Table.SelectRows( #"Changed Type", each [Year]=vCurYear-1),
CurYear = Table.SelectRows(#"Changed Type", each [Year]=vCurYear),
#"Merged Queries" = Table.NestedJoin(CurYear, {"customer", "prod"}, PY, {"customer", "prod"}, "PY", JoinKind.LeftOuter),
#"Expanded PY" = Table.ExpandTableColumn(#"Merged Queries", "PY", {"vol", "sales"}, {"PY.vol", "PY.sales"})
in
#"Expanded PY"

[Timo1980]

Thanks for the help all, both solutions worked!

[Anonymous · Accepted solution]

    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY67DYAwEEN3SU0RfJ8kJayB0gEzANNzh5AIUNiNLT9PU0DsS+jCth/mg6kHmbMAoXaffDSB2JwSK7eFeVnvAboGkEj/uQ9oEgcoCd0FxNcDUScIRZR/wRdySc4Rzm3ePLDAOPwGPA9wPcjmtZ4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, customer = _t, prod = _t, vol = _t, sales = _t]),
    #"Merge di colonne" = Table.CombineColumns(Origine,{"sales", "vol"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"vs"),
    #"Colonna trasformata tramite Pivot" = Table.Pivot(#"Merge di colonne", List.Distinct(#"Merge di colonne"[Year]), "Year", "vs"),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Colonna trasformata tramite Pivot", "2019", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"2019-sales", "2019-value"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"customer", type text}, {"prod", type text}, {"2019-sales", Int64.Type}, {"2019-value", Int64.Type}}),
    #"Suddividi colonna in base al delimitatore1" = Table.SplitColumn(#"Modificato tipo", "2020", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"2020-sales", "2020-value"}),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore1",{{"2020-sales", Int64.Type}, {"2020-value", Int64.Type}})
in
    #"Modificato tipo1"

[Anonymous]

if you use this script in your advanced editor

you get this result

[AlB]

Hi @Timo1980 

Can you share the first table in text format (just copy the table and paste it here)? So that we can copy the contents

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.