Forum Discussion

dengydongn's avatar
dengydongn
Microsoft Employee
5 years ago

How to avoid importing the same table again and again

Hello all,

 

I have this Spark SQL table as data input, and based on it I need to draw multiple charts, each chart requires different processing on the data (filtering, grouping, etc..), so for each chart, I imported the data source individually, whenever I refresh to pull latest data, the same table got read multiple times. Is there a way I can avoid this, to import the data source only once? 

 

Thanks

Need Help
Show and Tell

4 Replies

  • samdthompson's avatar
    samdthompson
    Memorable Member
    5 years ago

    Hello, import once then in power query reference the table  (right click on the query and reference) many times each with their own transformations applied. That way the Spark SQL is only hit once but you have all the variations you want.

     

     

    • dengydongn's avatar
      dengydongn
      Microsoft Employee
      5 years ago

      Thanks, this looks great. A further question, since I already have multiple queries each individually imports the same data source then applied various transformation, now I'm able to reference a data source, is there a way I can easily move one query from one source to another source (reference), or can I move steps applied on one source to another (reference), basically I'm trying to avoid manally creating all the steps on the reference source. Thanks

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    Generally, you shouldn't need multiple copies of the data just to create different visuals since most filtering and grouping can be handled with measures and dimensions within Power BI.

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    dengydongn in this case write a function and have all the logic in it, all the transformation steps, and pass source as a parameter and let this function do the magic and then use this function for each query, this way all the common steps are in the function and still can be used for different data sources. I hope it helps. If you never created a function in PQ, just google it and you will find my blog posts on it.

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡