Forum Discussion
Custom Column please help!!!!
Hi
Im hoping someone can help please!!!
I am trying to create 2 custom columns please see example sheet below.
I am wanting to return the max date when the "Postion" is the same value and < index number (doesnt have to have an index column thats just the way I was trying to solve it just has to be a prior date)
I am also wanting to return the ID also were there is a prioir date for that postion and date match
| date | Postion | ID | Index | Column 1 Return Date | Column 2 retun ID |
| 01/02/2022 | 1 | 1 | 1 | ||
| 01/02/2022 | 2 | 1 | 2 | ||
| 01/02/2022 | 3 | 1 | 3 | ||
| 01/02/2022 | 4 | 2 | 4 | ||
| 01/02/2022 | 5 | 2 | 5 | ||
| 02/02/2022 | 6 | 1 | 6 | ||
| 02/02/2022 | 1 | 1 | 7 | 01/02/2022 | 1 |
| 02/02/2022 | 7 | 1 | 8 | ||
| 02/02/2022 | 8 | 1 | 9 | ||
| 02/02/2022 | 2 | 3 | 10 | 01/02/2022 | 2 |
| 02/02/2022 | 9 | 3 | 11 | ||
| 03/02/2022 | 1 | 3 | 12 | 02/02/2022 | 1 |
| 03/02/2022 | 2 | 1 | 13 | 02/02/2022 | 3 |
| 03/02/2022 | 20 | 1 | 14 | ||
| 04/02/2022 | 24 | 2 | 15 | ||
| 04/02/2022 | 25 | 2 | 16 | ||
| 04/02/2022 | 3 | 2 | 17 | 01/02/2022 | 1 |
Hi Mattym80
According to your needs , I created two calculated columns .
Column = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]<EARLIER('Table'[date])))Column 2 = var _date='Table'[Column] RETURN CALCULATE(MAX('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]= _date))Add these two columns to your table , you will get a result like below .
In your result ,when "Postion" = 2 , "ID" = 3 , the return value should be 1 .
I have attached my pbix file , you can refer to it .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Mattym80 ,
In PowerQuery,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFLDsMgDESvErGOgjGfJBfoJSIWrdQLROqit29obSuI4Ep4gfzGeJhtM+CG2/Oxv+77e0BANKNxpyonj5cYo6hjnjCvY4EmBR2LhMUzhg2W6NGkY+xyPgqcBZws4kStjmYmzaKPXghbdUx+CHgFK52OZGVJlY+/NPflyuWYW0bX5lqNfIr/aVDW8V0JsKaKLrQgR+ziH5BDdkkHPXOSnxXvOX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Postion = _t, ID = _t, Index = _t, #"Column 1 Return Date" = _t, #"Column 2 retun ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Postion", Int64.Type}, {"ID", Int64.Type}, {"Index", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"date", "Postion", "ID", "Index"}), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Postion"}, {{"Rows", each _, type table [date=nullable date, Postion=nullable number, ID=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let AllList = Table.ToColumns([Rows]), ReturnDate = {null} & Table.RemoveLastN([Rows],1)[date], ReturnID = {null} & List.RemoveLastN(_[Rows][ID],1), CombineLists = AllList & {ReturnDate} & {ReturnID} , Columns = Table.ColumnNames([Rows]) in Table.FromColumns(CombineLists, Columns & {"Return Date", " Return ID"})), Expanded = Table.Combine(#"Added Custom"[Custom]), #"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Return Date", type date}, {" Return ID", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}) in #"Sorted Rows"
3 Replies
Hi Mattym80
According to your needs , I created two calculated columns .
Column = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]<EARLIER('Table'[date])))Column 2 = var _date='Table'[Column] RETURN CALCULATE(MAX('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]= _date))Add these two columns to your table , you will get a result like below .
In your result ,when "Postion" = 2 , "ID" = 3 , the return value should be 1 .
I have attached my pbix file , you can refer to it .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Mattym80 ,
In PowerQuery,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFLDsMgDESvErGOgjGfJBfoJSIWrdQLROqit29obSuI4Ep4gfzGeJhtM+CG2/Oxv+77e0BANKNxpyonj5cYo6hjnjCvY4EmBR2LhMUzhg2W6NGkY+xyPgqcBZws4kStjmYmzaKPXghbdUx+CHgFK52OZGVJlY+/NPflyuWYW0bX5lqNfIr/aVDW8V0JsKaKLrQgR+ziH5BDdkkHPXOSnxXvOX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Postion = _t, ID = _t, Index = _t, #"Column 1 Return Date" = _t, #"Column 2 retun ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Postion", Int64.Type}, {"ID", Int64.Type}, {"Index", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"date", "Postion", "ID", "Index"}), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Postion"}, {{"Rows", each _, type table [date=nullable date, Postion=nullable number, ID=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let AllList = Table.ToColumns([Rows]), ReturnDate = {null} & Table.RemoveLastN([Rows],1)[date], ReturnID = {null} & List.RemoveLastN(_[Rows][ID],1), CombineLists = AllList & {ReturnDate} & {ReturnID} , Columns = Table.ColumnNames([Rows]) in Table.FromColumns(CombineLists, Columns & {"Return Date", " Return ID"})), Expanded = Table.Combine(#"Added Custom"[Custom]), #"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Return Date", type date}, {" Return ID", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}) in #"Sorted Rows"
