Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

Power Query | How to Mark Consecutive Days

Hi There,

 

With a date column, how do I create a new column that tells that this current row(Date) is the day after the previous row.

 

Like if it were in Excel.. if(A2=(A1+1),"Consecutive","Hop"). I know there's DAX solution but I wanted to know if there is an M solution.

 

@ImkeF , @MarcelBeug , @Anyone

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]-2}[Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each try [Date]-[Custom] otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Result", each if [Custom.1] = null then null else if [Custom.1] = 1 then "Consecutive" else "Hop"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Custom", "Custom.1"})
in
    #"Removed Columns"

 

Untitled.png


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

View solution in original post

dax
Community Support
Community Support

Hi overteabejuela,

You also could refer to below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdSitWBc4yROabIHHNkjgUyxxKZY4hinCGKeUYGCJ4RsulG+mbIHKDpsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Date.AddDays([Date],-[Index])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"ALL", each _, type table [Date=date, Index=number, Custom=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([ALL], "a", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ALL"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Date", "a"}, {"Date", "a"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [a]<>1 then "Consecutive" else "Hop"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom", "a"})
in
    #"Removed Columns1"
 

Best Regards,
Zoe Zhi

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

 

View solution in original post

2 REPLIES 2
dax
Community Support
Community Support

Hi overteabejuela,

You also could refer to below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdSitWBc4yROabIHHNkjgUyxxKZY4hinCGKeUYGCJ4RsulG+mbIHKDpsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Date.AddDays([Date],-[Index])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"ALL", each _, type table [Date=date, Index=number, Custom=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([ALL], "a", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ALL"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Date", "a"}, {"Date", "a"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [a]<>1 then "Consecutive" else "Hop"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom", "a"})
in
    #"Removed Columns1"
 

Best Regards,
Zoe Zhi

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

 

Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]-2}[Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each try [Date]-[Custom] otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Result", each if [Custom.1] = null then null else if [Custom.1] = 1 then "Consecutive" else "Hop"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Custom", "Custom.1"})
in
    #"Removed Columns"

 

Untitled.png


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.