Flat tables vs Multidimensional in Power Query RRS feed

  • Question

  • Don't worry, I mean a table with columns of type table :-).

    So, does it matter for PQ whether it handles "multidimensional" table or not, from performance point of view?

    In other words, should I generally flatten a table before doing complex transformation, even if I have serious reasons not to do that?


    1. what is better in general - Table.ExpandColumn BEFORE complex evaluation or AFTER?

    2. What about Join? Does it matter for PQ whether tables have column of table type or not (of course I mean the case when keys are of scalar type)?

    3. Does NestedJoin is worse by design in comparison with Join?

    Sunday, July 28, 2019 1:25 PM

All replies

  • Generally nested columns of non-scalar values are treated lazily. So it shouldn't matter in what order you do things, unless the order affects folding. For example, if the "complex evaluation" you refer to above includes an operation that doesn't fold to SQL, it may be better to do the join+expand before the complex evaluation, in order to ensure that the join is performed on the SQL Server instead of locally.


    Monday, August 5, 2019 11:42 PM