Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Refer to row for a new column

I have a table with the first 4 columns and I would like to create the last column. What is the best way to go about this?    I have seen people use rank but will this work when I have more and mor...
Need Help
  • BenjaminSNN's avatar
    BenjaminSNN
    2 years ago

    Hey!

    I think this is best done by adding a custom column in Power Query.
    Basically you create a unique ID based on ID-Year-Month and a ID for previous month ID-Year-(Month -1) then you can join the query with itself to get values from the previous month within that ID.

    Code:

    let
    Source =YOURSOURCE,
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Quantity 1", Int64.Type}}),
    #"+ TempId" = Table.AddColumn(#"Changed Type", "TempID", each Text.From([ID]) & "-" & Text.From([Year]) & "-" & Text.From([Month])),
    #"+ TempId_prev" = Table.AddColumn(#"+ TempId", "TempId_prev", each Text.From([ID]) & "-" & Text.From([Year]) & "-" & Text.From([Month] - 1)),
    #"Self-join" = Table.NestedJoin(#"+ TempId_prev", {"TempId_prev"}, #"+ TempId_prev", {"TempID"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Self-join", "Added Custom", {"Quantity 1"}, {"Prior Month Quantity 1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Custom",null,0,Replacer.ReplaceValue,{"Prior Month Quantity 1"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"TempID", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"TempID", "TempId_prev"})
    in
    #"Removed Columns"

     



Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

Thanks BenjaminSNN  for the quick reply and solution. Please allow me to offer other idea:

We can create a column in PBI Desktop.

Prior Month Quantity 1 = 
var _date=DATE([Year],[Month],1)
var _last_date=EOMONTH(_date,-1)
var _qua= CALCULATE(SUM('Table'[Quantity 1]),FILTER('Table',[ID]=EARLIER('Table'[ID]) && [Year]=YEAR(_last_date)&& [Month]=MONTH(_last_date)))
RETURN IF(ISBLANK(_qua),0,_qua)

Then the result is as follows.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.