Date Pairing

[DuncanYeah]

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 Mar

How to pair the date column in Table B to Table A with the lastest date by power query.
like this 
Table A

1 Mar	1 Mar
2 Mar	1 Mar
3 Mar	1 Mar
4 Mar	4 Mar
5 Mar	4 Mar
6 Mar	4 Mar
7 Mar	7 Mar
8 Mar	7 Mar

Thank you 

Best Regards,

Duncan

[SundarRaj · Accepted solution]

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

[v-karpurapud]

Hi @DuncanYeah 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @DuncanYeah 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hello @DuncanYeah 

Could you please confirm if your query have been resolved the solution provided by @Deku ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[Deku]

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]

Dear @Deku ,

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

Duncan

[Deku]

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]

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"