Select a value with the filter of previous month and create a new column. Power Query (M code)

[Anonymous]

Hi everyone!

I have a trouble, I need to select the value of the previous month and create a new column with this values. The first value it is not important, it cna be 0.

I need do this in the Power Query Editor. 

This is an example. The first value of columnC it is not importan, the second one it has to be "1", the third "2", etc.

Date	ColumnA	ColumnB	ColumnC
01/01/2019	1	11
01/02/2019	2	22
01/03/2019	3	33
01/04/2019	4	44

Finally, I have to obteind something like this:

Date	ColumnA	ColumnB	ColumnC
01/01/2019	1	11	0
01/02/2019	2	22	1
01/03/2019	3	33	2
01/04/2019	4	44	3

Can anyone help me?

Thanks in advance

[Ashish_Mathur]

Hi,

This M code shows the value from column A of the previous row

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ColumnA", type text}, {"ColumnB", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Column A previous value", each Table.AddColumn(#"Added Index", "custom column", each #"Added Index"{[Index]-2}[ColumnA])),
    Custom = #"Added Custom"{0}[Column A previous value],
    #"Removed Columns" = Table.RemoveColumns(Custom,{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"custom column", "ColumnA previous value"}})
in
    #"Renamed Columns"

Hope this helps.

[ElenaN]

Hello,

You can create a new column that contains Previous Month Date values. Then you can make a merge of the table with itself, by left joining Previous Month column with your Date column. And when expanding choose to show Column A, and rename it to represent the value of the previous month. Then you can delete the Previous Month column in case you don't need it. Also, you can add additional conditions for the value of the first row as it is not mentioned if it doesn't matter if it is the first month in the calendar or the 1st month in each year.

Code attached:

 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Previous month", each Date.AddMonths([Date], -1)),
 #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Previous month"},#"Added Custom",{"Date"},"Added Custom",JoinKind.LeftOuter),
 #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"ColumnA"}, {"Added Custom.ColumnA"}),
 #"Renamed Columns1" = Table.RenameColumns(#"Expanded Added Custom",{{"Added Custom.ColumnA", "Previous month ColumnA"}}),
 #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Previous month"})

Regards,

ElenaN.

[Anonymous]

Thanks for the help, but when I write:

Table.AddColumn
(
#"Renamed Columns",
"Previous month",
each Date.AddMonths([Date], -1)
)

I have the next error:

"It can be find the name 'Renamed Columns'"

[ElenaN]

Yes, sorry, my mistake of not being more specific. The code i pasted was just the part that interests you. You need to replace 'Renamed columns' with the name of your previous step, before pasting my code. We always reference the name of the step we want to apply our logic to. In my case it was a step of renaming the columns. Hope this is more clear!

[Anonymous]

In Power Query --> Add Column --> Add Index Column, can start at 0 or 1

[Anonymous]

Thanks for the response Nick. But that it was only a example. I want select the value of the column that has the previous month. Imagine that it is not a number, but it is a letter or other thing.