>Hi,
>
>equal query is:
>

>SELECT CTETable.NoField,CTETable.NoField2,
>            (SELECT COUNT(*) FROM Client WHERE CTETable.NoField=Client.NoField AND CTETable.NoField2=Client.NoField2) AS Field3
> FROM (SELECT MAX(Client.NoField) AS NoField, MAX(Client.NoField2) AS NoField2,
>                FROM Client
>                GROUP BY Client.NoGroup
>           )  CTETable
>

Thanks, in order to extract Client fields in the final result, I need to include a MAX() into the ID at the CTE level and establish the INNER JOIN after :

SELECT Client.MyField,CTETable.NoField,CTETable.NoField2,
            (SELECT COUNT(*) FROM Client WHERE CTETable.NoField=Client.NoField AND CTETable.NoField2=Client.NoField2) AS Field3
 FROM (SELECT MAX(Client.ID) AS ID, MAX(Client.NoField) AS NoField, MAX(Client.NoField2) AS NoField2,
                FROM Client
                GROUP BY Client.NoGroup
           )  CTETable
 INNER JOIN Client ON CTETable.ID=Client.ID

However, the field I add, such as Client.MyField, generates :

Column 'Client.MyField' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This is confusing.

>But you can create view in db (with cte or not) and view use in Power BI.

That is correct. However, I wanted to get a query directly towards the tables.