Custom Function exponentiates the number of rows "internally"

[rodrigolynce]

Imagine this scenario:

I have 2 tables, such as one fact and one dimension, like this:

Dim_PRC

Anomes | Valor

201601  | PRC

201605  | PRC2

Fact_Hora

Hora | Anomes |  PRC | REAL | ORC

10     | 201601  | 1.2    |  1.5   |  2

I made a pivot in table Fact_Hora turning the columns "PRC, REAL, ORC" into rows, like this:

Fact_Hora

Hora | Anomes | Atributo | Values

10     | 201601  | PRC        | 1.2

10     | 201601  | REAL        | 1.5

10     | 201601  | ORC        | 2

I created a function called "getPRC" in a blank query, like this:

(codigo as text, tipo as text) =>

if tipo = "PRC" then
prc{[ANOMES=codigo]}[PRC]

else
tipo

Now, I applied this function on the table Fact_Hora into Atributo column, to bring the value of Dim_PRC table, the function works as well, but when I "apply and close" in the power query, the query stay running and the number of rows are growing, for example the source has 1.000 lines but it turns to more than 1.000.000 lines after the function were applied, however, when the power query closes the number of rows is correct.

The problem is the time that it takes to "process", is very strange.

The pbix file and the sources are in attached.

Files to test

https://1drv.ms/u/s!ArHpvmB5RhllkDSr7_kCeKDJ7f1c

Why it happens?

Thanks in advance.

Rodrigo

[v-shex-msft]

HI @rodrigolynce,

Based on test, I think these additional rows are caused with reference, each row refer to prc, so temporary table will increase to current row count * reference table row count.

Unfortunately, I haven't found any effective solution to solve these temporary rows.

@ImkeF @MarcelBeug Any idea about this scenario?

Regards,

Xiaoxin Sheng

[ImkeF · Accepted solution]

Hi @rodrigolynce,

by using your function on the fact table, you're referencing your dim table 600 k times. To make this faster, you have to buffer your dim table: Table.Buffer(YourTable)

But an even faster way to achieve your goal is to do a merge instead to retrieve the price for ALL rows and then add a custom column to define the condition like this:

    #"Merged Queries" = Table.NestedJoin(#"Valor Substituído1",{"MesId"},prc,{"ANOMES"},"prc",JoinKind.LeftOuter),
    #"Expanded prc" = Table.ExpandTableColumn(#"Merged Queries", "prc", {"PRC"}, {"PRC.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded prc", "TipoPRC", each if [Atributo] = "PRC" then [PRC.1] else [Atributo])

[rodrigolynce]

Hello ImkeF,

I did the merge and it's works perfectly, but, only for learning, can you give me a example of Table.Buffer in my function?

I tried this code below, but the problem still continues:

(codigo as text, tipo as text)=>

let
Fonte = Table.Buffer(prc)

in

if tipo = "PRC" then
Fonte{[ANOMES=codigo]}[PRC]
else
tipo

[rodrigolynce]

Hello again,

I solved the problem, I just put the Table.Buffer in the source of Dim_PRC table and it's works well..

Tranks for help.

Rodrigo