Forum Discussion
Date before the latest
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"
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"Yeeees! this helps.
thank you very much