cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.

DateColumnAColumnBColumnC
01/01/2019111 
01/02/2019222 
01/03/2019333 
01/04/2019444 

 

Finally, I have to obteind something like this:

 

DateColumnAColumnBColumnC
01/01/20191110
01/02/20192221
01/03/20193332
01/04/20194443

 

Can anyone help me?

 

Thanks in advance

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ElenaN
Resolver V
Resolver V

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
Not applicable

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'"

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
Not applicable

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

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors