Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have the following table in PowerQuery:
Date | Country | Value |
01/01/2000 | DE | 12345 |
01/01/2001 | DE | 6789 |
01/01/2000 | FR | 4567 |
01/01/2001 | FR | 1236 |
I would like to add a colomn with the year-to-year growth. This means that for each row, I would look up the value of the year before for the respective country and divide it and substract 1. I know how to do this with DAX, but in my use case I need to make use of M Query language and I am not finding any resources online. Could anyone help me with this?
Solved! Go to Solution.
You can use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDBQ0lFycQUShkbGJqZKsTpwGUOYjJm5hSWyhBFMwtQMVQJkllsQkDAxNTNHNwosAbTETCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Country = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Country", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"AllRows", each Table.AddIndexColumn(_,"Index",0,1)}}),
Custom1 = Table.AddColumn(#"Grouped Rows", "Custom", each let
DummyTbl = [AllRows], DummyList = List.Buffer(DummyTbl[Value]), Result = Table.AddColumn(DummyTbl,"YoY Growth", each try 100*([Value]-DummyList{[Index]-1})/DummyList{[Index]-1} otherwise null)
in
Result),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Country", "AllRows"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Country", "Value", "YoY Growth"}, {"Date", "Country", "Value", "YoY Growth"})
in
#"Expanded Custom"
You can use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDBQ0lFycQUShkbGJqZKsTpwGUOYjJm5hSWyhBFMwtQMVQJkllsQkDAxNTNHNwosAbTETCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Country = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Country", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"AllRows", each Table.AddIndexColumn(_,"Index",0,1)}}),
Custom1 = Table.AddColumn(#"Grouped Rows", "Custom", each let
DummyTbl = [AllRows], DummyList = List.Buffer(DummyTbl[Value]), Result = Table.AddColumn(DummyTbl,"YoY Growth", each try 100*([Value]-DummyList{[Index]-1})/DummyList{[Index]-1} otherwise null)
in
Result),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Country", "AllRows"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Country", "Value", "YoY Growth"}, {"Date", "Country", "Value", "YoY Growth"})
in
#"Expanded Custom"
Hello,
you might add a column like this
Date.AddYears([Date], -1)
To get the value from previous year
Then duplicate your table,
and link it with your country and value and [Date] With the newly created column [Date Last year]
It should do the trick?
🙂
(∩^o^)⊃━☆
The problem is that there will be multiple values that are the same as there are multiple countries with the same dates. So any kind of join will have to deal with multiple values that are the same.
If values are not identic it may have done the trick.
Anyway I've seen nice answer above 😌😅
Exactly, but the answer aboven does indeed solve the whole issue!