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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.