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
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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors