Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mattym80
New Member

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

Mattym80_0-1662664731791.png



datePostionIDIndexColumn 1 Return DateColumn 2 retun ID
01/02/2022111  
01/02/2022212  
01/02/2022313  
01/02/2022424  
01/02/2022525  
02/02/2022616  
02/02/202211701/02/20221
02/02/2022718  
02/02/2022819  
02/02/2022231001/02/20222
02/02/20229311  
03/02/2022131202/02/20221
03/02/2022211302/02/20223
03/02/202220114  
04/02/202224215  
04/02/202225216  
04/02/2022321701/02/20221
2 ACCEPTED SOLUTIONS
v-yetao1-msft
Community Support
Community Support

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 .

Ailsamsft_1-1662702654753.png

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.

 

 

View solution in original post

latimeria
Solution Specialist
Solution Specialist

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"

latimeria_0-1662706996135.png

 

View solution in original post

3 REPLIES 3
Mattym80
New Member

Hi @latimeria @v-yetao1-msft  Great stuff both examples were spot on,  great having 2 options now one for dax and one for M. Thank you both for your support.

latimeria
Solution Specialist
Solution Specialist

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"

latimeria_0-1662706996135.png

 

v-yetao1-msft
Community Support
Community Support

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 .

Ailsamsft_1-1662702654753.png

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors