- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Partially syndicated - Outbound Partially syndicated - Outbound](/html/assets/ics-partial-syndication-img.png)
TO FIND DATE COLUMN(NEXT DATE) BASED ON DATE COLUMN AND ACCOUNT CODE IN POWER QUERY
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
![avatar user](/skins/images/DD07B344B500A0750165C2AF212D539E/responsive_peak/images/icon_anonymous_message.png)
Helpful resources
Subject | Author | Posted | |
---|---|---|---|
08-25-2023 06:28 AM | |||
08-20-2024 06:48 PM | |||
08-08-2024 10:58 AM | |||
04-15-2024 11:09 AM | |||
08-08-2024 10:34 PM |