Help with Power Query

[Anonymous]

Hi,

I'm trying to do the following: I have a table with facts and a table with sales rate by province, year and quantity kilograms. I need to calculate a custom column in power query to get the value of the sales rate. Something like this in DAX:

Sales Rate = 
    CALCULATE(
        VALUES(TGRAL[Value]);
        FILTER(
            TGRAL;
            'Fact Merged Queries'[Destino.1] = TGRAL[IdProvincia] &&
            'Fact Merged Queries'[Kilos Volumen ORIGEN] >= TGRAL[DesdeKilos] &&
            'Fact Merged Queries'[Kilos Volumen ORIGEN] <= TGRAL[HastaKilos] &&
            YEAR('Fact Merged Queries'[Fecha Salida]) = TGRAL[IdRateYear]
        )
    )

Is there any way to do this in Power Query?

Thanks and best regards,

[v-lili6-msft · Accepted solution]

HI @Anonymous 

You could use this formula to add a custom column in power query

List.Sum(Table.SelectRows(TGRAL, 

(TGRAL) =>  (Date.Year(TGRAL[IdRateYear]) = [Fecha Salida])
and (TGRAL[IdProvincia]=[Destino.1])
and (TGRAL[DesdeKilos]<=[Kilos Volumen ORIGEN])
and (TGRAL[HastaKilos]>=[Kilos Volumen ORIGEN])

)[Value])

https://docs.microsoft.com/en-us/powerquery-m/table-selectrows

https://docs.microsoft.com/en-us/powerquery-m/list-sum

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft, 

Thanks so much for your help. I tried your solution and it works. Now i will decide if do it in DAX or M. 

Thanks again and regards. 

[ImkeF]

If you run into performance issues with that solution, use it on partitions instead: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power... 

[Anonymous]

Hi @ImkeF 

Thanks for your answer. I will see the link you posted. 

Regards,