Power Query with looping error

[bohaihk2022]

Hi,

I am writting a query with looping, but there is a error message I cannot figure it out what's wrong. Thanks for your help!

Below are the queries

Order

Order到期日

A	1/8/2022
B	1/10/2022
C	1/12/2022
D	1/1/2023
E	1/8/2023

// Test_Query
let
#"List" = {"Order1", "Order2"},
Source = Excel.CurrentWorkbook(){[Name="Order"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"到期日", type date}}),
#"Add Remaining Maturity Tenor Index" = Table.AddColumn(#"Changed Type", "Remaining Maturity Tenor Index", each List.Count(let val = [到期日] in List.Select(Tenor[Date], each _ <=val))-1 ),
#"Records of with Multiple Column" = Table.AddColumn(#"Add Remaining Maturity Tenor Index", "Order Tenor", each Record.Combine({Record.AddField([], List{0}, Table.Column(Tenor, List{0}){[Remaining Maturity Tenor Index]}), Record.AddField([], List{1}, Table.Column(Tenor, List{1}){[Remaining Maturity Tenor Index]})})),
#"Expanded {0}3" = Table.ExpandRecordColumn(#"Records of with Multiple Column", "Order Tenor", {"Order1", "Order2"}, {"Tenor.Order1", "Tenor.Order2"}),
#"New with Looping" = Table.AddColumn(#"Add Remaining Maturity Tenor Index", "OrderTenor", each Record.Combine(List.Generate(() => 0, each _ <= List.Count(#"List")-1, each _ +1, each Record.AddField([], List{_}, Table.Column(Tenor, List{_}){[Remaining Maturity Tenor Index]}))))
in
#"New with Looping"

Tenor

DateOrder1Order2

1/1/1900	Overdue	Next day or less
1/8/2022	Next day	Next day or less
2/8/2022	2-7 days	2-7 days
2/8/2022	2-7 days	2-7 days
2/8/2022	2-7 days	2-7 days
2/8/2022	2-7 days	2-7 days
2/8/2022	2-7 days	2-7 days
2/8/2022	2-7 days	2-7 days
2/8/2022	2-7 days	2-7 days
3/8/2022	2-7 days	2-7 days
8/8/2022	Between 8 days and 1 month	8 days to 1 month
9/8/2022	Between 8 days and 1 month	8 days to 1 month
1/9/2022	Between 1 and 3 months	1 to 3 months
1/11/2022	Between 3 months and 1 year	3 to 6 months
1/2/2023	Between 3 months and 1 year	6 to 12 months
1/8/2023	Later than 1 year	1 to 2 years
1/8/2024	Later than 1 year	2 to 3 years
1/8/2025	Later than 1 year	3 to 4 years
1/8/2026	Later than 1 year	4 to 5 years
1/8/2027	Later than 1 year	5 to 7 years
1/8/2029	Later than 1 year	7 to 10 years
1/8/2032	Later than 1 year	10 to 15 years
1/8/2037	Later than 1 year	15 to 20 years
1/8/2042	Later than 1 year	More than 20 years

// Tenor
let
Source = Excel.CurrentWorkbook(){[Name="Tenor"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
#"Changed Type"

[wdx223_Daniel · Accepted solution]

= let a =Table.Buffer(Table.Sort(Tenor,"Date")) in Table.FromRecords(List.Transform(Table.ToRecords(#"Changed Type"),each _&Record.SelectFields(Table.Last(Table.RemoveLastN(a,(x)=>x[Date]>[到期日])),#"List")))

[bohaihk2022]

It works! Many thanks! Could you please also share with me the meaning in each step? Thanks!

[Anonymous]

What is the error message you saw? Sharing it can help others understand easier.

[bohaihk2022]

it is in chinese, 

"Expression.Error: 無法對類型Number應用字段訪問。

詳細信息：

Value=0

Key=Remaining Maturity Tenor Index