Forum Discussion
Date before the latest
Hello smart guys,
I am trying to solve this in Power Query, because there are some other transformations related. Hope you can help.
Situation: Customers pay their order in instalments and each installments has Due Date (the date by when it should be paid) and Collected Date (the date when it's paid). If there is no CollectedDate, it means that instalment was not paid.
Goal: I want to get a table grouped by Orders which will give me:
- MinDueDate = Earliest Due Date (first instalment)
- LastPayDate = Latest Due Date if it is collected (latest paid instalment), ignore not paid instalments
- BeforeLastPayDate = Second Latest Due Date (instalment before the last paid)
Data can be found here
Thanks for looking at it.
Hi Anonymous ,
Please check the following steps as below.
1. Filter the table as below based on COLLECTED DATE column in power query.
2. Group the table by Order.
3. Insert a custom column as below.
4. After that, Merge the table based on Order and index column from both tables as the screen shot following.
5. Expand the due date column as we need and deleted the uncessary columns to get the excepted reuslt.
For more details, please check the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdNLDoJAEATQu8waw1TPj7mAiRsvQLj/NUyUtKVWx10TXoCpavY9XW/3C3LGyGlJwFpWy3l+z+lYfmkl6rNJ2oj6XCTtRH2ukg6iPjdJN6I+d0knUZ8HUzPY6z4yYboINFhDRkbaWJtM7dSnKTKu6IlVJkaaVwFNhkaatwH9X268EPDutkDzTsDrm3ES7w5ziIy6+0QN4r2GoF3XvI5mQbuueSOtBO0+9Wlq0C6bFnTKpusmaxnqy4Y+9Vvz72KbPrVrzny1Sac+Hg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"COLLECTED DATE" = _t, #"DUE DATE" = _t, instalment = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"COLLECTED DATE", type date}, {"DUE DATE", type date}, {"instalment", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([COLLECTED DATE] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order"}, {{"max due date", each List.Max([DUE DATE]), type date}, {"min collect date", each List.Min([COLLECTED DATE]), type date}, {"instalment", each List.Max([instalment]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [instalment]-1), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Order", "Custom"}, Table, {"Order", "instalment"}, "Table", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"DUE DATE"}, {"Table.DUE DATE"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"instalment", "Custom"}) in #"Removed Columns"
4 Replies
Hi Anonymous ,
Please create a calculated table as below.
Table 2 = VAR a = SUMMARIZECOLUMNS ( 'Table'[Order], FILTER ( 'Table', 'Table'[COLLECTED DATE] <> BLANK () ), "maxdate", MAX ( 'Table'[DUE DATE] ), "min", MIN ( 'Table'[COLLECTED DATE] ) ) RETURN ADDCOLUMNS ( a, "last", CALCULATE ( MAX ( 'Table'[DUE DATE] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Order] ), 'Table'[COLLECTED DATE] <> BLANK () && 'Table'[DUE DATE] <> [maxdate] ) ) )For more details, please check the pbix as attached.
Thank you, v-frfei-msft.
I need this in Power Query. The data is very big and i dont think it's smart to pull it in Power BI. Moreover, there are some other transformations there. Could you help building this in M language?
Hi Anonymous ,
Please check the following steps as below.
1. Filter the table as below based on COLLECTED DATE column in power query.
2. Group the table by Order.
3. Insert a custom column as below.
4. After that, Merge the table based on Order and index column from both tables as the screen shot following.
5. Expand the due date column as we need and deleted the uncessary columns to get the excepted reuslt.
For more details, please check the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdNLDoJAEATQu8waw1TPj7mAiRsvQLj/NUyUtKVWx10TXoCpavY9XW/3C3LGyGlJwFpWy3l+z+lYfmkl6rNJ2oj6XCTtRH2ukg6iPjdJN6I+d0knUZ8HUzPY6z4yYboINFhDRkbaWJtM7dSnKTKu6IlVJkaaVwFNhkaatwH9X268EPDutkDzTsDrm3ES7w5ziIy6+0QN4r2GoF3XvI5mQbuueSOtBO0+9Wlq0C6bFnTKpusmaxnqy4Y+9Vvz72KbPrVrzny1Sac+Hg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"COLLECTED DATE" = _t, #"DUE DATE" = _t, instalment = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"COLLECTED DATE", type date}, {"DUE DATE", type date}, {"instalment", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([COLLECTED DATE] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order"}, {{"max due date", each List.Max([DUE DATE]), type date}, {"min collect date", each List.Min([COLLECTED DATE]), type date}, {"instalment", each List.Max([instalment]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [instalment]-1), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Order", "Custom"}, Table, {"Order", "instalment"}, "Table", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"DUE DATE"}, {"Table.DUE DATE"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"instalment", "Custom"}) in #"Removed Columns"