Forum Discussion

DuncanYeah's avatar
DuncanYeah
Helper III
1 year ago
Solved

Date Pairing

Dear All, I have two date columns from two different table.  Table 1 A  1 Mar  2 Mar 3 Mar 4 Mar  5 Mar  6 Mar  7 Mar  8 Mar   Table B  1 Mar  4 Mar  7 M...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    Hi DuncanYeah , another solution you can look at. I'll leave the image of output and M code used. Thanks!

    let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    MainTable = Table.TransformColumnTypes(Source,{{"Table A", type datetime}}),
    Dates = Excel.CurrentWorkbook(){[Name = "Table6"]}[Content][#"Table B "],
    Match = Table.AddColumn(MainTable,"Date", each try Dates{List.PositionOf(Dates,Record.ToList(_){0})} otherwise null),
    Table = Table.FillDown(Match,{"Date"})inTable

     

Deku's avatar
Deku
Super User
1 year ago

Assuming in date format

let
    Source = #"Table A",
    #"Merged Queries" = Table.AddColumn( Source, "x", each #"Table B"[Date] ),
    #"Expanded x" = Table.ExpandListColumn(#"Merged Queries", "x"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded x", each ([x] < [Date]))
in
    #"Filtered Rows"
  • DuncanYeah's avatar
    DuncanYeah
    Helper III
    1 year ago

    Dear Deku ,

    Thx for your reply but there are error
    Expression.Error: A cyclic reference was encountered during evaluation.

    Duncan

    • Deku's avatar
      Deku
      Super User
      1 year ago

      I ran it in a new table, table c. If you want it to run in table a you need to remove the

      Source = #"Table A" and update #"Merged Queries" to reference the last step in your current query

       

      • Deku's avatar
        Deku
        Super User
        1 year ago

        Like this

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTwTSxSitWJVjKCs4zhLBMQSwHMNEUwzRBMcwTTAqIrFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Merged Queries" = Table.AddColumn( #"Changed Type", "x", each #"Table B"[Date] ),
    #"Expanded x" = Table.ExpandListColumn(#"Merged Queries", "x"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded x", each ([x] < [Date]))
in
    #"Filtered Rows"