Sharing Variables between two sources

[Mic1979]

Dear all,

I have the following code:

let
Source1 = OVERALL_DISTRIBUTION,
#"Filtered Rows" = Table.SelectRows(Source1, each ([Integrated_Check_Valve] = "Integrated Check Valve NO") and ([Connection_Type_Front_Port] = "Female Thread") and ([Voltage] = "24VDC") and ([Valve_Function] = "Collector") and ([Sealing_Material] = "FPM") and ([Pipe_Size_Front_Port] = "1/2 inch")),

Source2 = Component_List,
Name_Column = Source2 [Name],
Breakdown_Numbers = List.Count (Name_Column),

New = Table.AddColumn(#"Filtered Rows", "List", each {1..[Breakdown_Numbers]})

in New

As you can see, my target is to use the variable Breakdown_Numbers (related to the source 2) to add a column List in the Source1.

However I got the following error:

Expression.Error: The field 'Breakdown_Numbers' of the record wasn't found

I think this is something related to the variable delcaration, but I am not very familiar with the topic.

Hoping you can support.

Thanks.

[OwenAuger · Accepted solution]

Hi @Mic1979 

Remove the square brackets around Breakdown_Numbers in the New step:

New = Table.AddColumn(#"Filtered Rows", "List", each {1 .. Breakdown_Numbers})

A reference to another variable within a let expression doesn't require square brackets.

With the square brackets, [Breakdown_Numbers] was attempting to access a field named Breakdown_Numbers within the rows of #"Filtered Rows", which would work only if #"Filtered Rows" contained a column named Breakdown_Numbers.

See here:

https://bengribaudo.com/blog/2018/10/30/4644/power-query-m-primer-part10-types-list-record#field-acc...

[Mic1979]

PERFECT!!

Thanks