Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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"
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"
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"
Thanks for the help all, both solutions worked!
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"
if you use this script in your advanced editor
you get this result
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.