Unable to combine data - revisited

[AdamJustyn]

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

[v-kelly-msft]

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 · Accepted solution]

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]

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!