Return value from current table to a subquery (Power Query)

[PietroFarias]

Hey guys!

I want the column of a "subquery" to be equal to the column of the current table.

Sample:

= Table.AddColumn(
	NbrMonth,
	"NmbCustomer",
	each
		Table.RowCount(
			Table.SelectRows(
				Table.Distinct(
					Table.SelectColumns(
						NbrMonth,{"Store","Op", "NF"}
					)
				),
			each ([NF] = [NF]) //I want the [NF] column of this "subquery" to be equal to [NF] of the NbrMonth table.
		)
	) 

[OwenAuger · Accepted solution]

Hi @PietroFarias

This should do the trick:

= Table.AddColumn(
    NbrMonth,
    "NmbCustomer",
    each
        let OuterNF = [NF]
        in
        Table.RowCount(
            Table.SelectRows(
                Table.Distinct(
                    Table.SelectColumns(
                        NbrMonth,{"Store","Op", "NF"}
                    )
                ),
                each ([NF] = OuterNF)
            )
        )
    )

 Regards,

Owen

[OwenAuger · Accepted solution]

Hi @PietroFarias

This should do the trick:

= Table.AddColumn(
    NbrMonth,
    "NmbCustomer",
    each
        let OuterNF = [NF]
        in
        Table.RowCount(
            Table.SelectRows(
                Table.Distinct(
                    Table.SelectColumns(
                        NbrMonth,{"Store","Op", "NF"}
                    )
                ),
                each ([NF] = OuterNF)
            )
        )
    )

 Regards,

Owen

[PietroFarias]

@OwenAuger

It work! Great.
I'm a newbie in M. Can you explain me why the "LET"?

[OwenAuger · Accepted solution]

Glad it worked 🙂

Sure - a "let expression" is used to capture values from intermediate calculations before returning a result.

It takes the form

let
   variable1 = expression1,
   variable2 = expression2,
   ...
in
   result_expression

In your example, I used let OuterNF = [NF] to store the value of [NF] from the current row so that I could refer to it in the result expression. The resulting "let expression" formatted a little differently was:

let
   OuterNF = [NF]
in
   Table.RowCount(
      Table.SelectRows(
         Table.Distinct(
            Table.SelectColumns(
               NbrMonth,{"Store","Op", "NF"}
            )
         ),
         each ([NF] = OuterNF)
      )
   )

Here is a link to the Power Query M language specification which might be useful for reference - see page 85 for let expressions:

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification

Best regards,

Owen