Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Lookup values for year before of each country

Hi everyone,

 

I have the following table in PowerQuery:

DateCountryValue
01/01/2000  DE12345
01/01/2001  DE6789
01/01/2000  FR4567
01/01/2001  FR1236

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?

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

5 REPLIES 5
Vijay_A_Verma
Super User
Super User

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"
SebSchoon1
Post Patron
Post Patron

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^)⊃━☆

Anonymous
Not applicable

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 😌😅

Anonymous
Not applicable

Exactly, but the answer aboven does indeed solve the whole issue!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors