Forum Discussion

AdamJustyn's avatar
AdamJustyn
Frequent Visitor
6 years ago
Solved

Unable to combine data - revisited

Hello Community Members,

I'm digging the Internet to find out the solution to my problem.

Finnally I've found out Patrick's Guy in the Cube video:

https://youtu.be/iiNDq2VrZPY   (title: Dynamic Power BI reports using Parameters)

I need the solution just like in Patrick's video: Excel file and sql server. I carefully do the steps from the video and in PB Desktop everything works well including data refresh.

But when I published my report into PB service I got an error:

Unable to combine data] Section1/RM Items/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

And I'm wondering:

- if Patrick's video is outdated or just fairy-tale 🙂

- if PB service changed since video publishing and the video is not valid any more (does that mean: more security vs less functionality);

- if in 2020 exists the solution to request similar to the video and where I could find it.

 

Thanks for your patient.

Regards

AdamJustyn

Need Help
Show and Tell
  • AdamJustyn's avatar
    AdamJustyn
    6 years ago

    Thanks Kelly.

    I've given a try the solution proposed here and to my suprise it works.

    Up to now I was convinced that every data source has to be created in a separate PQ table (this was also the solution in Patrick's video). But the working solution (in Power BI service) require mixing Excel data and sql server data in one PQ table.

    So I can confirm that if we prepare a selection table in Excel (stored locally or on OneDrive) and then use this table (after small transformation) as a condition in sql server query where clause the solution will work when both sources will be read in the same PQ table.

     

    Regards

    AdamJustyn 

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi AdamJustyn ，

     

    Could you pls make a copy of the codes in the advanced editor?

     


     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • AdamJustyn's avatar
      AdamJustyn
      Frequent Visitor
      6 years ago

      Thanks Kelly.

      I've given a try the solution proposed here and to my suprise it works.

      Up to now I was convinced that every data source has to be created in a separate PQ table (this was also the solution in Patrick's video). But the working solution (in Power BI service) require mixing Excel data and sql server data in one PQ table.

      So I can confirm that if we prepare a selection table in Excel (stored locally or on OneDrive) and then use this table (after small transformation) as a condition in sql server query where clause the solution will work when both sources will be read in the same PQ table.

       

      Regards

      AdamJustyn 

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi AdamJustyn ,

         

        Good point,thanks for sharing!

        So your issue is solved now?If so,could you pls mark the reply as answered to close it?

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!