Forum Discussion
How to merge two get nearest previous date by using M Code ?
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
This code is for Table2.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1NFWK1YFxgQiViyJrDBRB5RoqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}), BuffList = List.Buffer(Table1[Column1]), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [l=List.Transform(BuffList,(x)=>Duration.Days(x-[Column1])),p=BuffList{List.PositionOf(l,List.Last(List.Select(l,(y)=>y<=0)))}][p]) in #"Added Custom"Code for Table1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MAQipVgdGNdI19AAiWsMFFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}) in #"Changed Type"You have done in the right manner. As supplying both input and output makes us give right answer.
You will need to use a try otherwise block
try [l=List.Transform(BuffList,(x)=>Duration.Days(x-[Column1])),p=BuffList{List.PositionOf(l,List.Last(List.Select(l,(y)=>y<=0)))}][p] otherwise List.Max(BuffList)Hence, following code for Table2
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1NFWK1YFxgQiViyJrDBRB5RrCuEBzjMDcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}), BuffList = List.Buffer(Table1[Column1]), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try [l=List.Transform(BuffList,(x)=>Duration.Days(x-[Column1])),p=BuffList{List.PositionOf(l,List.Last(List.Select(l,(y)=>y<=0)))}][p] otherwise List.Max(BuffList)) in #"Added Custom"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
This code is for Table2.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1NFWK1YFxgQiViyJrDBRB5RoqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
BuffList = List.Buffer(Table1[Column1]),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [l=List.Transform(BuffList,(x)=>Duration.Days(x-[Column1])),p=BuffList{List.PositionOf(l,List.Last(List.Select(l,(y)=>y<=0)))}][p])
in
#"Added Custom"Code for Table1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MAQipVgdGNdI19AAiWsMFFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}})
in
#"Changed Type"If you make sure BuffList is sorted, then you can simply take the last element that's less than or equal to the date in the current row.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1NFWK1YFxgQiViyJrDBRB5RoqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), type table [Column1 = text]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}), BuffList = List.Buffer(List.Sort(Table1[Column1])), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (row) => List.Last(List.Select(BuffList, each _ <= row[Column1])), type date) in #"Added Custom"AlexisOlson
Thank you！I’ll try that too!
Vijay_A_Verma
Thank you for helping me.I didn't know this solution.
<List.Buffer(Table1[Column1])>Thank you 😊
Vijay_A_Verma
I am sorry for asking again.I got the error for date before Table1.
Could you please advice me how to change formula?
I would like to get last date(2022/03/02) in Table1 when date is prior to 2022/01/01.
Table1:
Expected Result :
Table2 (added Column1 form Table2):
I know how to replace the error value, but I would like to know how to do it in this formula if possible😣
Solved: Query Editor-Replace Errors in Date column with la... - Microsoft Power BI CommunityThe way you showed input and output in your original problem statement, in the same way you need to post your requirement.
Vijay_A_Verma
I have edited my post.Sorry if my understanding is different.
I am not a native speakes so I'm not sure if I understand it properly..😓
