TO FIND DATE COLUMN(NEXT DATE) BASED ON DATE COLUMN AND ACCOUNT CODE IN POWER QUERY

[SharmilaBrisca]

My data contains 4 column Date,Account Code,Company and Amount.Now I want to create new column in power query editor and the new column should return the next date based on Date, Account Code and Company.....

For Example: In First Row for Account Code-89888 and Company 234 Date is 01-03-2023, now the new column(Next Date) which I create should return the next following date(01-07-2023) for the same Account Code and Company 
If there is no Next following date for the Particular Account Code and Company then it should return year end date for the particular date

All these needs to be done in Power Query Editor

[dufoq3 · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9LDoAgDEXRvTDWpB+gr2sx7n8blhhMozhpJye9cByFeCfdhUTLVuAAYovWmA6icm636dM4DD6s9ZjWk/FkusVutY1rLRlbt2A/d1JLU4t53eLcev7FLE4yYsTj0XF0mvpj4K87/DWiLyOLVgtzXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Account Code" = _t, Company = _t, Amount = _t]),
    YourSource = Source,
    AddedIndex = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    Ad_Year = Table.AddColumn(AddedIndex, "Year", each Date.Year(Date.From([Date])), Int64.Type),
    GroupedRows = Table.Group(Ad_Year, {"Account Code", "Company", "Year"}, {{"All1", each _, type table}, {"All2", each _, type table}}),
    ExpandedAll1 = Table.ExpandTableColumn(GroupedRows, "All1", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"}),
    SortedRows = Table.Sort(ExpandedAll1,{{"Index", Order.Ascending}}),
    Ad_NextDate = Table.AddColumn(SortedRows, "Next Date", each 
        Date.From(
            Record.FieldOrDefault(
                Table.SelectRows(
                    [All2],
                    (r)=> r[Index] > [Index]
                ){0}?,
                "Date",
                Date.StartOfMonth(Date.EndOfYear(Date.From([Date])))
            )
        ), type date),
    RemovedOtherColumns = Table.SelectColumns(Ad_NextDate,{"Date", "Account Code", "Company", "Amount", "Next Date"}),
    ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"Date", type date}, {"Account Code", Int64.Type}, {"Company", Int64.Type}, {"Amount", Currency.Type}})
in
    ChangedType

[dufoq3]

Is this what are you looking for?

Edit 2nd step YourSource = Source (change Source to your table)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY3LCQAhDER7yVkhMZpPLWL/bWx0WcjBvczA8Jg3JyBV5NqwMRQwN7Poxj3SDRFWeRkJhvmsar5ZlUiVxPj3E4xo9Ohjv43E6N1l+vOTXJxcRHcXHdd6AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Account Code" = _t, Company = _t, Amount = _t]),
    YourSource = Source,
    AddedIndex = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    GroupedRows = Table.Group(AddedIndex, {"Account Code", "Company"}, {{"All1", each _, type table}, {"All2", each _, type table}}),
    ExpandedAll1 = Table.ExpandTableColumn(GroupedRows, "All1", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"}),
    SortedRows = Table.Sort(ExpandedAll1,{{"Index", Order.Ascending}}),
    Ad_NextDate = Table.AddColumn(SortedRows, "Next Date", each 
        Date.From(
            Record.FieldOrDefault(
                Table.SelectRows(
                    [All2],
                    (r)=> r[Index] > [Index]
                ){0}?,
                "Date",
                Date.StartOfMonth(Date.EndOfYear(Date.From([Date])))
            )
        ), type date),
    RemovedOtherColumns = Table.SelectColumns(Ad_NextDate,{"Date", "Account Code", "Company", "Amount", "Next Date"}),
    ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"Date", type date}, {"Account Code", Int64.Type}, {"Company", Int64.Type}, {"Amount", Currency.Type}})
in
    ChangedType

[SharmilaBrisca]

Hi,
Thanks for Helping me with solution but if the following date is not there in a particular year then it should be end of year date(01-12-year)while I am implementing this concept i am getting this output

I am expecting the below output

[dufoq3 · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9LDoAgDEXRvTDWpB+gr2sx7n8blhhMozhpJye9cByFeCfdhUTLVuAAYovWmA6icm636dM4DD6s9ZjWk/FkusVutY1rLRlbt2A/d1JLU4t53eLcev7FLE4yYsTj0XF0mvpj4K87/DWiLyOLVgtzXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Account Code" = _t, Company = _t, Amount = _t]),
    YourSource = Source,
    AddedIndex = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    Ad_Year = Table.AddColumn(AddedIndex, "Year", each Date.Year(Date.From([Date])), Int64.Type),
    GroupedRows = Table.Group(Ad_Year, {"Account Code", "Company", "Year"}, {{"All1", each _, type table}, {"All2", each _, type table}}),
    ExpandedAll1 = Table.ExpandTableColumn(GroupedRows, "All1", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"}),
    SortedRows = Table.Sort(ExpandedAll1,{{"Index", Order.Ascending}}),
    Ad_NextDate = Table.AddColumn(SortedRows, "Next Date", each 
        Date.From(
            Record.FieldOrDefault(
                Table.SelectRows(
                    [All2],
                    (r)=> r[Index] > [Index]
                ){0}?,
                "Date",
                Date.StartOfMonth(Date.EndOfYear(Date.From([Date])))
            )
        ), type date),
    RemovedOtherColumns = Table.SelectColumns(Ad_NextDate,{"Date", "Account Code", "Company", "Amount", "Next Date"}),
    ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"Date", type date}, {"Account Code", Int64.Type}, {"Company", Int64.Type}, {"Amount", Currency.Type}})
in
    ChangedType

[SharmilaBrisca]

Hi @dufoq3 
Can You Please Have a look on this

My data contains 4 column Date,Account Code,Company and Amount.Now I want to create new column in power query editor and the new column should return the next date in a Particular Financial Year(starts from April - Ends at March)based on Date, Account Code and Company.....
Financial Year-Example (Starts from 01-04-2023 to End 01-03-2024)

For Example: In First Row for Account Code-89888 and Company 234 Date is 01-03-2023, now the new column(Next Date) which I create should return the same date(01-03-2023-because financial year end) for the same Account Code and Company 
In Second Row for Account Code-98789 and Company 876 Date is 01-06-2023, now the new column(Next Date) which I create should return the following date in the same Financial Year(01-09-2023) for the same Account Code and Company 
If there is no Next following date  for the Particular Account Code and Company in a Particular Financial Year then it should return year end date(01-03-20..)

All these needs to be done in Power Query Editor

[dufoq3 · Accepted solution]

Hi @SharmilaBrisca,

Your 2nd picture is too small so I can't read it. Is this the expected result?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9LDoAgDEXRvTDWpB+gr2sx7n8blhhMozhpJye9cByFeCfdhUTLVuAAYovWmA6icm636dM4DD6s9ZjWk/FkusVutY1rLRlbt2A/d1JLU4t53eLcev7FLE4yYsTj0XF0mvpj4K87/DWiLyOLVgtzXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Account Code" = _t, Company = _t, Amount = _t]),
    YourSource = Source,
    AddedIndex = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    Ad_FiscalYear = Table.AddColumn(AddedIndex, "Fiscal Year", each if Date.Month(Date.From([Date])) < 4 then Date.Year(Date.From([Date])) -1 else Date.Year(Date.From([Date])), Int64.Type),
    GroupedRows = Table.Group(Ad_FiscalYear, {"Account Code", "Company", "Fiscal Year"}, {{"All1", each _, type table}, {"All2", each _, type table}}),
    ExpandedAll1 = Table.ExpandTableColumn(GroupedRows, "All1", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"}),
    SortedRows = Table.Sort(ExpandedAll1,{{"Index", Order.Ascending}}),
    Ad_NextDate = Table.AddColumn(SortedRows, "Next Date", each 
        Date.From(
            Record.FieldOrDefault(
                Table.SelectRows(
                    [All2],
                    (r)=> r[Index] > [Index]
                ){0}?,
                "Date",
                #date([Fiscal Year] +1, 3, 1)
            )
        ), type date),
    RemovedOtherColumns = Table.SelectColumns(Ad_NextDate,{"Date", "Account Code", "Company", "Amount", "Next Date"}),
    ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"Date", type date}, {"Account Code", Int64.Type}, {"Company", Int64.Type}, {"Amount", Currency.Type}})
in
    ChangedType

[SharmilaBrisca]

Hi @dufoq3 It is working....Thank you so much

[slorin]

Hi,

A solution with Table.Group

let
Source = Your_Source,
Groupe = Table.Group(Source, {"Account Code", "Company"},
      {{"Data", each Table.Sort(_, {{"Date", Order.Ascending}})}}),
Data = Table.AddColumn(Groupe, "Data2", each Table.FromColumns(
      {[Data][Date], [Data][Amount], List.Skip([Data][Date])&{null}},
      {"Date", "Amount", "Next Date"})),
Select_Column = Table.SelectColumns(Data,{"Account Code", "Company", "Data2"}),
Expand = Table.ExpandTableColumn(Select_Column, "Data2", {"Date", "Amount", "Next Date"}, {"Date", "Amount", "Next Date"})
in
Expand

Stéphane