cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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

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

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

 

Highlighted
New Member

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

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.

Highlighted
Resolver IV
Resolver IV

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

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.

Highlighted
Super User IV
Super User IV

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

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/
Highlighted
New Member

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

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

Highlighted
Resolver IV
Resolver IV

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

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!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors