Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

'Group by' refreshes data

I get data from 6 different data sources and put them in 6 tables. I then combine (Table.combine) the 6 tables. They all have a specific column common. When I try to do 'Group by' on that column, the data from the 6 sources refreshes again (with the first refresh at the time of combining the data). This makes the whole refresh very slow for the combined table.
Is is possible to set that it doesn't refresh the data the second time (Group by)? Please help.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous 

     

    Does this relate to what you experience in PQ Editor or this is when you refresh the dataset after it is exported to the Designer?

    In PQ Editor this may be a norm as it reads and refreshes underlying datasets when it thinks it needs it.

     

    I tested the Table.Combine()  behaviour and it looks like it reads every data source just once, assuming none of the underlying tables is exposed/returned from the Editor. If you return the underlying tables as well as the combined table, it will read the data source twice: the first time for the table and then for the combined table (unless you are doing this in PBI Dataflow). Could you please check with the view of the above?

     

    Kind regards,

    JB

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Anonymous,

       

      It's not the Table.Combine, but Table.Group that reads the data the second time. I am talking about the refresh in the Desktop, not the PQ Editor itself. So once the query has applied changes, I want to refresh the data daily. When I select the combined table and try to refresh, it refreshes all the 6 tables twice, once at Table.Combine and then at Table.Group step.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Hi mate,

        Sounds a bit strange. I tested grouping too, and it did not produce an additional data import from files, therefore I decided not to update my reply.

        Try Table.Buffer () each imported table before using in Table.Combine. But if they are large enough to create a substantial delay when loading it may make the overall execution time longer even if it helps reducing the number of loads.

        For my knowledge, how do you trace the execution? As far as I know PBI does not have a debugger and it's virtually impossible to tell what happens at what stage. I will do more testing, so it also would be great to know what is the type of your data source? Excel files?

        Thanks
        JB