Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
inside PQ I've created the A table with some transformations, that loads data from a csv file.
After, I've created the B table as a reference one from A.
Inside the B table I've added a custom column by invoking a custom function that uses Table.SelectRows and Table.Max functions against the A table. It seems that for each custom column value in the B table it occurs a data loading from the A table: the data loading dimension of the B table with the custom column is of some hundreds of megabytes while the data loading dimension of the table B without the custom column is of some hundreds of kilobytes.
I could use a DAX expression to calculate the custom column but I would need to apply some other transformation depending on this column inside PQ and not at a model level.
Any suggests to me in order to solve this issue, please? Thanks
You can try buffering the TableA before using Table.SelectRows and Table.Max expressions.
So you can write something like
BuffTableA = TableA
Stepx = Table.SelectRows(BuffTableA.......
Hi,
inside the query for the A table, I've added as a last step something like #"Table buffering" = Table.Buffer(#"previous step", [BufferMode = BufferMode.Delayed]). So, the data loading dimension for the table B is remained the same one as without the custom column, also if it is increased the data loading time.
Using Table.Buffer could be a good solution, but I think to hope using this function in the right manner.
I've said that the A table is created to connect to the csv source and to apply some initial transformations; then I've created the B table as a reference one from A. In this way, I can inside the B table I can to invoke a custom function against the A table avoiding a circular reference. Now, is it right to apply the Table.Buffer function to the A table or is it right to create a new C table as a reference one from A and to apply the Table.Buffer function to the C table (obviously, the custom function has to consider the C table)?
Many thanks
Without seeing your Query for TableA and TableC, I will not be able to advise on the most optimum solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.