Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to merge two get nearest previous date by using M Code ?

Hi. I am trying to get nearest previous date from other table(Table1) in Power Query not by DAX?

 

Table1:

 

Expected Result :

Table2 (added Column1 form Table2):

 

 

Any help on how to do this would be highly appreciated.

Thank you.

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

9 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"
    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      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"

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Vijay_A_Verma 
      Thank you for helping me.

      I didn't know this solution.
      <List.Buffer(Table1[Column1])>

      Thank you 😊

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 Community

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        The way you showed input and output in your original problem statement, in the same way you need to post your requirement.